sql - 将对象插入 SQL Server 中的 JSON 数组

Question

我见过的 JSON_MODIFY 的每个示例都显示将一个简单的值(例如字符串)插入到数组中。

假设我的 SQL Server 列中存储了以下 JSON:

[{"id": 1, "name": "一"}, {"id": 2, "name": "二"}]

如何附加 {"id": 3, "name": "Three"} 到它？

当我尝试使用如下所示的 JSON_MODIFY 时，会插入一个 string:

UPDATE TheTable SET TheJSON = JSON_MODIFY(TheJSON, 'append $', N'{"id": 3, "name": "Three"}') WHERE Con​​dition = 1;

以下是 TheJSON 列的结果值:

[{"id": 1, "name": "一"}, {"id": 2, "name": "二"}, "{\"id\":3,\"名称\":\"三\"}"]

其他尝试:

我注意到我可以像这样创建我想要的 JSON 字符串:

SELECT json.*
FROM TheTable t
CROSS APPLY OPENJSON(t.TheJSON) WITH (
    id int N'$.id',
    name nvarchar(100) N'$.name'
)
UNION ALL
SELECT 3 as id, N'Three' as name
FOR JSON AUTO;

但是，当我尝试在更新语句中使用它时，它不起作用:

UPDATE TheTable
SET TheJSON = (
    SELECT json.* FROM TheTable t
    CROSS APPLY OPENJSON(t.TheJSON) WITH (
        id int N'$.id',
        name nvarchar(100) N'$.name'
    ) as json
    UNION ALL -- NO ERROR (and no update) when I remove UNION ALL+SELECT
    SELECT 3 as id, N'Three' as name
    FOR JSON AUTO
);

我收到以下错误:

Msg 1086, Level 15, State 1, Line 1: The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.

Answer 1

您应该使用 JSON_QUERY() 包装 JSON_MODIFY 语句的第三个参数:

UPDATE TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

这是一个完整的示例:

DECLARE @TheTable table(TheJSON nvarchar(max), Condition int )
DECLARE @mystring nvarchar(100)='{"id": 3, "name": "Three"}'

INSERT INTO @TheTable SELECT '[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]', 1

UPDATE @TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

SELECT TheJSON FROM @TheTable

这是最终的输出:

[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"},{"id": 3, "name": "Three"}]

有关 JSON_QUERY 的更多信息 here ，问题的解释是here .