将对象插入SQL Server中的JSON数组 [英] Insert an object into a JSON array in SQL Server

问题描述

我在JSON_MODIFY中看到的每个示例都显示了将简单的值(例如string)插入到数组中.

Every example that I've seen for JSON_MODIFY shows inserting a simple value such as a string into an array.

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

Suppose I have the following JSON stored in my SQL Server column:

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

如何在其后附加{"id": 3, "name": "Three"}?

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

When I try using JSON_MODIFY as shown below, a string is inserted:

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字符串:

I noticed that I can create the JSON string that I want like this:

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;

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

However, when I go to try and use it in an update statement, it doesn't work:

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.

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.

推荐答案

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

You should wrap the third parameter of your JSON_MODIFY statement with 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的更多信息 ，问题的说明是

More info on JSON_QUERY here, and the explation of the issue is here.

这篇关于将对象插入SQL Server中的JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！