JSON - How to append an array to an array in sql

Question

I have a json field in a table that contains an array like this:-

[
  {
    "ID": 11111,
    "Name": "apple",
  },
  {
    "ID": 22222,
    "Name": "orange",
  },
  {
    "ID": 333333,
    "Name": "banana",
  } 
] 

I would like to append/concatenate the following json array to this one:-

[
  {
    "ID": 44444,
    "Name": "grape",
  },
  {
    "ID": 55555,
    "Name": "kiwi",
  },
  {
    "ID": 66666,
    "Name": "fig",
  } 
] 

So that I end up with this in the table field:-

[
  {
    "ID": 11111,
    "Name": "apple",
  },
  {
    "ID": 22222,
    "Name": "orange",
  },
  {
    "ID": 333333,
    "Name": "banana",
  },
  {
    "ID": 44444,
    "Name": "grape",
  },
  {
    "ID": 55555,
    "Name": "kiwi",
  },
  {
    "ID": 66666,
    "Name": "fig",
  } 
] 

i.e. I've added the three new elements to the three existing elements so that i now have a single array with six elements in my table field.

I have been trying to make this work with JSON_MODIFY and have been successful in adding a single element to the array with something like this:-

select JSON_MODIFY(json_field,'append $', JSON_QUERY('{ "ID": 44444, "Name": "grape" }'))

But I cannot make it append more than one element in a single operation and make it look as desired, I've been trying variations of this:-

select JSON_MODIFY(json_field,'append $', JSON_QUERY('[{ "ID": 44444, "Name": "grape" }, { "ID": 55555, "Name": "kiwi" }, { "ID": 66666, "Name": "fig" }]'))

In this particular case, it appended it with the square brackets so the three new elements ended up being a sub-array!

Is it possible to get append multiple elements of one array to another like this? (Am I being really thick and missing something obvious?!?)

Answer 1

From the documentation:

Example - Multiple updates: With JSON_MODIFY you can update only one property. If you have to do multiple updates, you can use multiple JSON_MODIFY calls.

This would mean looping, something I'd try to avoid...

I'd suggest either a simple string action or de-composition/re-composition:

DECLARE @json1 NVARCHAR(MAX)=
N'[
  {
    "ID": 11111,
    "Name": "apple"
  },
  {
    "ID": 22222,
    "Name": "orange"
  },
  {
    "ID": 333333,
    "Name": "banana"
  } 
]'; 
DECLARE @json2 NVARCHAR(MAX)=
N'[
  {
    "ID": 44444,
    "Name": "grape"
  },
  {
    "ID": 55555,
    "Name": "kiwi"
  },
  {
    "ID": 66666,
    "Name": "fig"
  } 
]';

--This will re-create the JSON out of derived tables

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

--This will create a naked array and STUFF() it into the correct position

DECLARE @NakedArray NVARCHAR(MAX)=N',' +
(
    SELECT A.* 
    FROM OPENJSON(@json2)
    WITH(ID int, Name NVARCHAR(MAX)) A
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

SELECT STUFF(@json1,LEN(@json1)-1,0,@NakedArray);

The naked array you could achieve simply by replacing the [ with a comma and cut away the ] too...

UPDATE: A minimal approach

Try this minimal approach:

SELECT REPLACE(@json1,']',REPLACE(@json2,'[',','));

Answer 2

Try this:

DECLARE @j1 AS NVARCHAR(MAX) = '[
  {
    "ID": 11111,
    "Name": "apple"
  },
  {
    "ID": 22222,
    "Name": "orange"
  },
  {
    "ID": 333333,
    "Name": "banana"
  } 
] ';


DECLARE @j2 AS NVARCHAR(MAX) = '
[
  {
    "ID": 44444,
    "Name": "grape"
  },
  {
    "ID": 55555,
    "Name": "kiwi"
  },
  {
    "ID": 66666,
    "Name": "fig"
  } 
] ';


SELECT * FROM
(
SELECT [ID], [Name] FROM  OPENJSON(@j1) WITH (ID INT, [Name] NVARCHAR(200))
UNION
SELECT  [ID], [Name]  FROM  OPENJSON(@j2) WITH (ID INT, [Name] NVARCHAR(200))
) x
FOR JSON AUTO