CODEWITHSUNDEEP
jsont-sql
Kevin Mettes
Kevin Mettes

Reputation: 13

TSQL how to merge two Json arrays

How to merge new array elements into an existing Json property? Our two json objects look like this and we want to append the items of the 2nd json object to the 1st json object. (See desired result)

Is there a way to achieve this with JSON_MODIFY? The microsoft documentation doesn't really show any example of multiple elements being merged into the already existing array. Only a single element. But we have a list of multiple elements that need to be merged.

Edit: JSON_MODIFY(@json1, 'append $.Items', JSON_QUERY(@json2)) seems to create new array brackets instead of merging the items into the array.

Json Object 1:

DECLARE @json1 NVARCHAR(MAX) = '{
    "id": 1,
    "Items": [
        {
            "id": 1,
            "name" : "Item #1"
        },
        {
            "id": 2,
            "name" : "Item #2"
        }
    ]
}'

Json Object 2:

DECLARE @json2 NVARCHAR(MAX)='{
    "Items": [
        {
            "id": 3,
            "name": "Item #3"
        },
        {
            "id": 4,
            "name": "Item #4"
        }
    ]
}'

Desired Result:

{
    "id": 1,
    "Items": [
        {
            "id": 1,
            "name": "Item #1"
        },
        {
            "id": 2,
            "name": "Item #2"
        },
        {
            "id": 3,
            "name": "Item #3"
        },
        {
            "id": 4,
            "name": "Item #4"
        }
    ]
}

Upvotes: 1

Views: 7013

Answers (3)

RustyNail
RustyNail

Reputation: 11

Not pretty but this will merge text and object elements. I'm sure it isn't bullet proof. Offered only as a potential solution.

Declare @json1 nvarchar(max) = '{"id":1,"messages":[{"type":"Info","text":"message1"},{"type":"Info","text":"message2"}]}'
Declare @json2 nvarchar(max) = '{"id":1,"messages":["justPlanText",{"type":"Info","text":"message3"},{"type":"Info","text":"message4"}]}'

select @json1 = case 
                when isjson(m.value) = 1 then
                  json_modify(@json1,'append $.messages',json_query(m.value))
                else 
                  json_modify(@json1,'append $.messages',m.value)
                end
from openjson(json_query(@json2,'$.messages')) m

select @json1

Results for @json1:

{"id":1,"messages":[{"type":"Info","text":"message1"},{"type":"Info","text":"message2"},"justPlanText",{"type":"Info","text":"message3"},{"type":"Info","text":"message4"}]}

Upvotes: 1

Anyvado
Anyvado

Reputation: 71

Your problem can be solved by

SELECT  dbo.udf_native_json_merge(@json1,@json2,null)

We faced similar issues trying to merge JSONs in MS SQL. We also wanted it to be recursive and allow us to define a strategy for arrays like "union", "concat" and "replace".

Our solution for JSON manipulations like merge, JSON path expressions and more is open source and available @ Github

Feel free to use, comment and contribute so we can further improve JSON methods for MS SQL.

Upvotes: 4

Zohar Peled
Zohar Peled

Reputation: 82474

It's Cumbersome but possible to achieve with SQL Server's built it JSON support.

First, set proper sample data (Please save us this step in your future questions):

DECLARE @Json1 nvarchar(max) = 
'{
    "id": 1,
    "Items": [
        {
            "id": 1,
            "name" : "Item #1"
        },
        {
            "id": 2,
            "name" : "Item #2"
        }
    ]
}',

@Json2 nvarchar(max) = 
'{
    "Items": [
        {
            "id": 3,
            "name": "Item #3"
        },
        {
            "id": 4,
            "name": "Item #4"
        }
    ]
}';

Then, wrap a union all query containing openjson and json_query for each one of the variables with a common table expression:

With cteArray as
(
    SELECT *
    FROM OPENJSON(JSON_QUERY(@Json1, '$.Items'))
    WITH(
        Id int '$.id',
        Name varchar(100) '$.name'
    )
    UNION ALL 
    SELECT *
    FROM OPENJSON(JSON_QUERY(@Json2, '$.Items'))
    WITH(
        Id int '$.id',
        Name varchar(100) '$.name'
    )
)

The result of that union all query is this:

Id      Name
1       Item #1
2       Item #2
3       Item #3
4       Item #4

Then, select the id from the first json using json_value, and add a subquery to select everything from the cte with for json path. Add another for json path and specify without_array_wrapper to the outer query:

SELECT JSON_VALUE(@Json1, '$.id') As id,
        (
            SELECT * 
            FROM cteArray
            FOR JSON PATH
        ) as Items
FOR JSON PATH,
WITHOUT_ARRAY_WRAPPER

The final result:

{
    "id": "1",
    "Items": [{
            "Id": 1,
            "Name": "Item #1"
        }, {
            "Id": 2,
            "Name": "Item #2"
        }, {
            "Id": 3,
            "Name": "Item #3"
        }, {
            "Id": 4,
            "Name": "Item #4"
        }
    ]
}

You can see a live demo on Db<>Fiddle

Upvotes: 3

Related Questions