Format valid JSON for associate object in SQL Server

Question

This is my query:

SELECT 
    a.Id, a.Title,
    (SELECT m.Id, m.Name, m.MobileNo
     FROM [msm].[MsmMember] AS m
     WHERE m.Id = a.MemberId 
       AND m.OrganizationId = @OrganizationId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Member
FROM 
    msm.MsmArticleSubmission a 
WHERE
    a.OrganizationId = @OrganizationId
    AND a.Id = @Id
FOR JSON PATH

This is the resulting output:

[
  {
    "Id": "A4C6B579-5D0C-4807-9537-53C6B1E8E6BA",
    "Title": "Test Data",
    "Member": "{\"Id\":\"79F87A71-332A-43CB-A944-E8C50C00CFF7\",\"Name\":\"Asma Husain Noora\",\"MobileNo\":\"00000000\"}"
  }
]

Here Member is not a valid JSON Format, so, when I try to deserialize from C#, the Member object throws an exception, because of the invalid JSON format. I couldn't figure out any solution. It works if I make "Member" an Array, but I need a single associate object only.

Tech stack: ASP.NET Core 8.0, SQL Server 2019

Answer 1

try this :

SELECT 
    a.Id, a.Title,
    JSON_QUERY((SELECT m.Id, m.Name, m.MobileNo
     FROM [msm].[MsmMember] AS m
     WHERE m.Id = a.MemberId 
       AND m.OrganizationId = @OrganizationId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Member
FROM 
    msm.MsmArticleSubmission a 
WHERE
    a.OrganizationId = @OrganizationId
    AND a.Id = @Id
FOR JSON PATH