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

Question

我看到的每个示例都JSON_MODIFY显示了将简单的值（例如a）string插入到数组中。

假设我在SQL Server列中存储了以下JSON：

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

我该如何追加{"id": 3, "name": "Three"}呢？

当我尝试JSON_MODIFY如下所示使用时，将string插入一个：

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

这是TheJSON列的结果值：

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

其他尝试：

我注意到我可以这样创建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
);

我收到以下错误：

消息1086，级别15，状态1，第1行：当FOR XML和FOR
JSON子句包含set运算符时，它们在视图，内联函数，派生表和子查询中无效。要解决此问题，请使用派生表或公用表表达式或视图包装包含set运算符的SELECT并在其之上应用FOR
XML或FOR JSON。

Answer 1

您应该将JSON_MODIFY语句的第三个参数包装为JSON_QUERY()：

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 此的更多信息，有关此问题的说明，请参见此处。