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]

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;

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"

Table "t2"

RESULT

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