Insert an object into a JSON array in SQL Server

Question

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

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

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

How do I append {"id": 3, "name": "Three"} to it?

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;

Here is the resulting value for TheJSON column:

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

Other Attempts:

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 );

I get the following error:

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.

I suppose that I could just use a table-variable to hold my JSON data temporarily, insert into it and then select from that with FOR JSON ~ I tried just that and it worked fine. So, I will watch this question to see if there is an even simpler method. — Wayne Bloss
– Wayne Bloss, Commented Dec 21, 2017 at 2:36

Andrea · Accepted Answer · 2022-03-31 07:05:28Z

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;

Here is a complete sample:

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

This is the final output:

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

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

Collectives™ on Stack Overflow

Insert an object into a JSON array in SQL Server

Other Attempts:

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Other Attempts:

1 Answer 1

Comments

Linked

Related