Question 304222 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Format valid JSON for associate object in SQL Server

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search