CODEWITHSUNDEEP
sqljsonsql-servert-sql
Zarna Borda
Zarna Borda

Reputation: 735

Append Multiple Objects into Existing Array Using JSON_MODIFY

The problem was in appending new JSON array to the existing JSON array:

Suppose I have the following JSON Array [{"id": 1, "data": "One"}, {"id": 2, "data": "Two"}]

How do I append [{"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}] to it using JSON_MODIFY?

resulting value for updated column: [{"id": 1, "data": "One"}, {"id": 2, "data": "Two"}, {"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}]

Upvotes: 8

Views: 10072

Answers (3)

Andrew Elans
Andrew Elans

Reputation: 83

Workaround I found for my project

I have two tables t1 and t2 which are identical in structure. Table t1 keeps records of supplier certificates. Table t2 gets with API new certificates obtained by supplier. So Table t1 shall be updated with new certificates from Table t2. Certificates data are placed in JSON array of objects, similar to the example of the topic starter.

Task

JSON array in t1 col JSON_t1 shall be appended with JSON array from t2 col JSON_t2. Here's the structure simplified for the example purposes:

Table "t1"

recordId JSON_t1
1 [{"id": 1, "data": "One"}, {"id": 2, "data": "Two"}]

Table "t2"

recordId JSON_t2
1 [{"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}]

RESULT

appended t1.JSON_t1
[{"id": 1, "data": "One"}, {"id": 2, "data": "Two"},{"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}]

SQL method

SELECT
t1.JSON_t1,
t2.JSON_t2,
concat('[', replace(replace(json_modify(t1.JSON_t1, 'append $', json_query(t2.JSON_t2)), '[', ''), ']', ''), ']') as "appended t1.JSON_t1"
FROM t1
INNER JOIN t2 ON t1.recordId = t2.recordId

Method explained

  1. JSON_t2 is converted to JSON format with json_query(t2.JSON_t2) to avoid escaping of characters

  2. JSON_t1 is appended with JSON_t2 with json_modify(t1.JSON_t1, 'append $', json_query(t2.JSON_t2)) resulting to the following output: [{"id": 1, "data": "One"}, {"id": 2, "data": "Two"},[{"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}]]. Note square brackets in bold which shall be fixed as this will not be a correct final array of objects.

  3. Final JSON is stripped from all square brackets with replace method used two times: for "[" and "]" replace(replace(json_modify(t1.JSON_t1, 'append $', json_query(t2.JSON_t2)), '[', ''), ']', '')

  4. Final JSON is added with two square brackets at start and end to make a valid JSON array concat('[', replace(replace(json_modify(t1.JSON_t1, 'append $', json_query(t2.JSON_t2)), '[', ''), ']', ''), ']')

  5. You can test if the final JSON is valid with ISJSON()

Points to note

  • If you don't use json_query, you get the following result: [{"id": 1, "data": "One"}, {"id": 2, "data": "Two"},"[{\"id\": 3, \"data\": \"Three\"}, {\"id\": 4, \"data\": \"Four\"}]"]. See more on this here.
  • I tried to strip from square brackets only JSON_t2 and use json_modify like this json_modify(t1.JSON_t1, 'append $', json_query('{"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}') but this results to appending only the first item from the JSON_t2 like this: [{"id": 1, "data": "One"}, {"id": 2, "data": "Two"},{"id": 3, "data": "Three"}]
  • NB. if you have nested arrays, this method is not suitable. In my case it works well since I have a simple array of objects (certificates with various key/value pairs like validity, type, issue date, etc.)

Upvotes: 1

Zhorov
Zhorov

Reputation: 29943

I don't think, that you can merge two JSON arrays with one JSON_MODIFY() call, but the following statement (using JSON_MODIFY()) is a possible solution:

Statement:

DECLARE @json NVARCHAR(500)='[{"id": 1, "data": "One"}, {"id": 2, "data": "Two"}]'
DECLARE @new NVARCHAR(500)='[{"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}]'

SELECT @json = JSON_MODIFY(
   @json,
   'append $',
   JSON_QUERY([value])
)
FROM OPENJSON(@new)

SELECT @json

Result:

[{"id": 1, "data": "One"}, {"id": 2, "data": "Two"},{"id": 3, "data": "Three"},{"id": 4, "data": "Four"}]

Upvotes: 8

Vikram Jain
Vikram Jain

Reputation: 5588

You can use "JSON_MODIFY" function and append key to modify JSON object like below:

SQL-FIDDLE

It's for individual JSON array:

DECLARE @json1 NVARCHAR(500)='[{"id": 1, "data": "One"}, {"id": 2, "data": "Two"}]';
DECLARE @json2 NVARCHAR(500)='[{"id": 3, "data": "Three"}, {"id": 4, "data": "Four"}]';


SELECT t.id, t.[data]
FROM
(
    SELECT * FROM OPENJSON(@json1) WITH(id int,[data] NVARCHAR(MAX)) 
    UNION ALL
    SELECT * FROM OPENJSON(@json2) WITH(id int,[data] NVARCHAR(MAX))
) t
FOR JSON PATH;

It's for individual JSON hash:

DECLARE @info NVARCHAR(500)='[{"id": 1, "data": "One"}, {"id": 2, "data": "Two"}]';

PRINT @info;

SET @info = JSON_MODIFY(@info, 'append $', JSON_QUERY('{"id": 3, "data": "Three"}'))
SET @info = JSON_MODIFY(@info, 'append $', JSON_QUERY('{"id": 4, "data": "Four"}'))

PRINT @info;

Upvotes: 1

Related Questions