Append Multiple Objects into Existing Array Using JSON_MODIFY

Question

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"}]

Answer 1

Original answer:

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) = N'[{"id": 1, "data": "One"}, {"id": 2, "data": "Two"}]'
DECLARE @new NVARCHAR(500)  = N'[{"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"}]

Update:

SQL Server 2025 will introduce some new JSON features and one of them is the JSON_ARRAYAGG() function:

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

DECLARE @json NVARCHAR(max) 
SELECT @json = JSON_ARRAYAGG([value])
FROM (
    SELECT JSON_QUERY([value]) AS [value]
    FROM OPENJSON(@old)
    UNION ALL
    SELECT JSON_QUERY([value]) AS [value]
    FROM OPENJSON(@new)
) t
SELECT @json

Result:

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

With this approach you can easily parse and aggregate JSON arrays containing scalar values (not JSON objects) with defined data type:

DECLARE @old NVARCHAR(max) = N'[1, 2]'
DECLARE @new NVARCHAR(max) = N'[3, 4]'

DECLARE @json NVARCHAR(max) 
SELECT @json = JSON_ARRAYAGG([value])
FROM (
    SELECT [value]
    FROM OPENJSON(@old) WITH ([value] int '$')
    UNION ALL
    SELECT [value]
    FROM OPENJSON(@new) WITH ([value] int '$')
) t
SELECT @json

Result:

[1,2,3,4]

Answer 2

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;