CODEWITHSUNDEEP
jsonsql-servert-sqlsql-server-2016
Storm
Storm

Reputation: 2022

Multiple SELECT statements into a single JSON

I'm convinced this must be answered somewhere but for the life of me I just can't seem to find anything no matter how much I change my search phrases.

I need to select data from two completely independent tables and export the information to JSON. In this case, they're both 1 record in each table.

If I select just 1 at a time and export to JSON, they're 1 record, but when I join the two single records in SQL and then export to JSON, they're 1 record arrays.

Just 1 record SQL Input:

DECLARE @Json nvarchar(max) = 
(   
    SELECT 'Data1' AS [Data1], 'Data2' AS [Data2]

    FOR JSON PATH
    , INCLUDE_NULL_VALUES
    , WITHOUT_ARRAY_WRAPPER
);

SELECT @Json;
GO

Just 1 record JSON Output (note there's no array):

{
  "Data1": "Data1",
  "Data2": "Data2"
}

2 record SQL Input:

DECLARE @Json nvarchar(max) = 
(   
    SELECT
    (
        SELECT 'Data1' AS [Data1], 'Data2' AS [Data2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
    ) AS [Part1]
    ,
    (
        SELECT 'Text1' AS [Text1], 'Text2' AS [Text2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
    ) AS [Part2]

    FOR JSON PATH
    , WITHOUT_ARRAY_WRAPPER
);

SELECT @Json;
GO

2 record JSON Output (note the inclusion of arrays):

{
  "Part1": [
    {
      "Data1": "Data1",
      "Data2": "Data2"
    }
  ],
  "Part2": [
    {
      "Text1": "Text1",
      "Text2": "Text2"
    }
  ]
}

I "think" that WITHOUT_ARRAY_WRAPPER is the correct attribute to add which will resolve this but as soon as I add that, I get the entire record as a string:

{
  "Part1": "{\"Data1\":\"Data1\",\"Data2\":\"Data2\"}",
  "Part2": "{\"Text1\":\"Text1\",\"Text2\":\"Text2\"}"
}

I understand that there's text manipulation methods I can use to get this to work, but I'm hoping for a clean SQL > JSON statement.

I'm currently working on SQL Server 2016 but I can if necessary get a 2017 or 2019 server. Not sure if later SQL handles this better or if it's just my query that needs optimisation.

Edit: My desired output is:

{
  "Part1": {
      "Data1": "Data1",
      "Data2": "Data2"
    },
  "Part2": {
      "Text1": "Text1",
      "Text2": "Text2"
    }
}

Upvotes: 2

Views: 7860

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

According to the accepted answer of FOR JSON PATH. how to not use escape characters on SQL Server's forum on MSDN:

FOR JSON will escape any text unless if it is generated as JSON result by some JSON function/query. In your example, FOR JSON cannot know do you really want raw JSON or you are just sending some free text that looks like JSON.

Properly defined JSON is generated with FOR JSON (unless if it has WITHOUT_ARRAY_WRAPPER option) or JSON_QUERY. If you wrap your JSON literal with JSON_QUERY it will not be escaped.

This answer got me to try the following code:

DECLARE @Json nvarchar(max) = 
(
    SELECT
     JSON_QUERY((
        SELECT 'Data1' AS [Data1], 'Data2' AS [Data2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
        , WITHOUT_ARRAY_WRAPPER
    )) AS [Part1]
    ,
    JSON_QUERY((
        SELECT 'Text1' AS [Text1], 'Text2' AS [Text2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
        , WITHOUT_ARRAY_WRAPPER
    )) AS [Part2]

    FOR JSON PATH
    , WITHOUT_ARRAY_WRAPPER
);

SELECT @Json;

As as it turns out - this is working like a charm. Results:

{
    "Part1": {
        "Data1": "Data1",
        "Data2": "Data2"
    },
    "Part2": {
        "Text1": "Text1",
        "Text2": "Text2"
    }
}

DB<>Fiddle

Update Look what I found buried in official documentation:

To avoid automatic escaping, provide newValue by using the JSON_QUERY function. JSON_MODIFY knows that the value returned by JSON_MODIFY is properly formatted JSON, so it doesn't escape the value.

Upvotes: 5

Related Questions