Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert an object into a JSON array in SQL Server

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.

like image 367
Wayne Bloss Avatar asked Dec 21 '17 02:12

Wayne Bloss


People also ask

Can JSON array contain object?

Yes, json arrays can contain any valid json string: objects with different key/value pairs, other arrays, numbers, strings, booleans all in the same array.

How do I add a JSON object to a database?

Click the Add button and select Column. On the Column element, specify values for the Index and Value attributes. Click the Add button in the sub-menu and select Add Same. Repeat the last two steps to add additional columns and elements from the JSON file.

Can we insert JSON data into SQL Server?

Now, to import JSON data in the SQL server, we will use OPENROWSET (BULK). It is a table-valued function that can read data from any file.. It returns a table with a single column that contains all the contents of the file. It can just load the entire contents of a file as a text value.

Can SQL store JSON object?

You can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database.


1 Answers

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.

like image 104
Andrea Avatar answered Oct 26 '22 20:10

Andrea