Parse JSON text inside NVARCHAR column when using FOR JSON PATH

Question

Suppose that I have a table with a records like this:

CREATE TABLE json_store (
    [id] INT IDENTITY(1, 1) PRIMARY KEY,
    [json_data] NVARCHAR(MAX)
)

id  |   json_data
------------------------------------------
1   |   {"data_in_json": "Hello from 1"}
2   |   {"data_in_json": "Hello from 2"}
3   |   {"data_in_json": "Hello from 3", "remarks": "I have a different structure"}

and I want a JSON result like this:

[
    {
        "id": 1,
        "json_data": {"data_in_json": "Hello from 1"}
    },
    {
        "id": 2,
        "json_data": {"data_in_json": "Hello from 2"}
    },
    {
        "id": 3,
        "json_data": {"data_in_json": "Hello from 3", "remarks": "I have a different structure"}
    }
]

---

I've been using FOR JSON PATH but the problem is it returns JSON as an escaped string.

SELECT [id], [json_data]
FROM [json_store]
FOR JSON PATH

[
    {
        "id": 1,
        "json_data": "{\"data_in_json\": \"Hello from 1\"}"
    },
    {
        "id": 2,
        "json_data": "{\"data_in_json\": \"Hello from 2\"}"
    },
    {
        "id": 3,
        "json_data": "{\"data_in_json\": \"Hello from 3\", \"remarks\": \"I have a different structure\"}"
    }
]

I wanted to know is it possible to parse a JSON column along with the result?

Also, I've tried using OPENJSON() to parse and use FOR JSON PATH convert it back to JSON but it does break the structure into [{"key": ..., "value": ...}, ...]

Answer 1

You need to convert your NVARCHAR(MAX) column json_data to JSON first, using JSON_QUERY, so that SQL Server knows it is already JSON and won't escape it:

CREATE TABLE #T (id INT, json_data NVARCHAR(MAX));
INSERT #T(id, json_data)
VALUES
    (1, '{"data_in_json": "Hello from 1"}'),
    (2, '{"data_in_json": "Hello from 2"}'),
    (3, '{"data_in_json": "Hello from 3", "remarks": "I have a different structure"}');


SELECT  id, JSON_QUERY(t.json_data) AS json_data
FROM    #T AS t
FOR JSON AUTO;

Which gives:

[
    {
        "id": 1,
        "json_data": {
            "data_in_json": "Hello from 1"
        }
    },
    {
        "id": 2,
        "json_data": {
            "data_in_json": "Hello from 2"
        }
    },
    {
        "id": 3,
        "json_data": {
            "data_in_json": "Hello from 3",
            "remarks": "I have a different structure"
        }
    }
]