sql - 使用 FOR JSON PATH 时解析 NVARCHAR 列中的 JSON 文本

Question

假设我有一个包含如下记录的表:

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"}

我想要这样的 JSON 结果:

[
    {
        "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"}
    }
]

---

我一直在使用 FOR JSON PATH 但问题是它将 JSON 作为转义字符串返回。

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\"}"
    }
]

我想知道是否可以将 JSON 列与结果一起解析？

此外，我尝试使用 OPENJSON() 来解析并使用 FOR JSON PATH 将其转换回 JSON，但它确实将结构分解为 [ {“键”:...，“值”:...}，...]

Answer 1

您需要先将NVARCHAR(MAX) 列json_data 转换为JSON，使用JSON_QUERY ，以便 SQL Server 知道它已经是 JSON 并且不会转义它:

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;

给出:

[
    {
        "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"
        }
    }
]