Format valid JSON for associate object in SQL Server

The issue you’re facing stems from the fact that the Member sub-query is producing a JSON string that’s then being treated as a standard string in the context of the outer JSON query. Instead of the inner JSON being naturally integrated into the outer JSON, it’s being escaped and included as a simple string.

One way to solve this is to parse the inner JSON string in the resultant JSON object in your application. However, this is not an ideal solution as it’s not efficient and requires unnecessary parsing.

The better solution is to format the JSON correctly in your SQL query itself, so that the Member object is a nested JSON object within the outer JSON, rather than a JSON-formatted string. Unfortunately, SQL Server doesn’t have a built-in function that directly casts a JSON string to a JSON object in the way that you want.

Here’s a technique you can use to integrate the inner JSON into the outer JSON by using the OPENJSON and CROSS APPLY clauses:

SELECT 
    a.Id, a.Title,
    Member.*
FROM 
    msm.MsmArticleSubmission a 
-- Extract members as JSON and reparse it to integrate into the main JSON
CROSS APPLY (
    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 MemberJSON (JsonContent)
CROSS APPLY (
    SELECT *
    FROM OPENJSON(MemberJSON.JsonContent) 
    WITH (
        Id UNIQUEIDENTIFIER '$.Id',
        Name NVARCHAR(255) '$.Name',
        MobileNo NVARCHAR(50) '$.MobileNo'
    )
) AS Member
WHERE
    a.OrganizationId = @OrganizationId
    AND a.Id = @Id
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Here’s what’s happening in this query:

1. The CROSS APPLY takes the sub-query JSON string and exposes it as a named JSON Content (MemberJSON.JsonContent).
2. Another CROSS APPLY with OPENJSON then parses that JSON string back into SQL rows, with the WITH clause casting the JSON values back to their proper SQL types and names.
3. The query then selects from this parsed JSON directly (Member.*), which allows the JSON to be integrated without additional string escaping.

With this technique, Member should now be a properly integrated nested JSON object in your result, so when you query, it will return a valid JSON object instead of a JSON string for Member.