TSQL JSON How to add an array to an existing Json Object?

Question

We've got a SQL query where we create a JSON file with for JSON Path. We want to merge 2 JSON objects into 1 JSON. But we struggle with the code how to accomplish this task.

We tried JSON_MODIFY to merge them together using append. But this did not work for us.

What we'd like to do is this, we have 2 seperate json objects and we want to merge them as one.

Json Object A:

{
    "ID" : 0,
    "Name" : "a name",
    "Description" : "a description"
}

and Json Object B

"Nodes" : [
    {
        "NodeID" : 10,
        "NodeName" : "Node 0"
    },
    {
        "NodeID" : 11,
        "NodeName" : "Node 1"
    }
]

What we want to have:

{
    "ID" : 0,
    "Name" : "a name",
    "Description" : "a description",
    "Nodes" : [
        {
            "NodeID" : 10,
            "NodeName" : "Node 0"
        },
        {
            "NodeID" : 11,
            "NodeName" : "Node 1"
        }
    ]
}

Our current SQL Query looks like this:

set @JsonCourse = ( select c.name, c.id, c.description from dbo.courses c where c.id = @id for json path)

set @JsonNodes = ( select n.id, n.name from dbo.nodes n where n.courseId = @id for json path, root('Nodes'))

set @CompleteJson =  JSON_MODIFY(@JsonCourse,'append $',JSON_QUERY(@JsonNodes));

print @CompleteJson

But our result is like this:

[
    {
        "ID" : 0,
        "Name" : "a name",
        "Description" : "a description" 
    },
    {
        "Nodes" : [
            {
                "NodeID" : 10,
                "NodeName" : "Node 0"
            },
            {
                "NodeID" : 11,
                "NodeName" : "Node 1"
            }
        ]
    }
]

Note: we've used hypothetical data here.

How do we fix this with JSON_MODIFY?

Gottfried Lesigang · Accepted Answer · 2019-07-11 16:26:03Z

1

So I'll add another answer, as this is a completely different thing as the first answer:

As I do not have your tables, I'll set the JSON variables to the values you provided

DECLARE @json1 NVARCHAR(MAX)=
N'{
    "ID" : 0,
    "Name" : "a name",
    "Description" : "a description"
}'

DECLARE @json2 NVARCHAR(MAX)= --<-- had to add the surrounding {}, otherwise this was invalid JSON
N'{"Nodes" : 
[
    {
        "NodeID" : 10,
        "NodeName" : "Node 0"
    },
    {
        "NodeID" : 11,
        "NodeName" : "Node 1"
    }
]}';

--We do not need append here.
--We have to tell the engine the name of the new node.
--To avoid repeated key Nodes I read from the @json2 using $.Nodes as path

DECLARE @CompleteJSON NVARCHAR(MAX)=JSON_MODIFY(@Json1,'$.Nodes',JSON_QUERY(@Json2,'$.Nodes'));

PRINT @CompleteJSON;

I hope this is closer to your needs...

answered Jul 11, 2019 at 16:26

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Mettes Over a year ago

Yes this is exactly what we need, thanks a lot for the explanation! Now I understand JSON_MODIFY better

Gottfried Lesigang · Accepted Answer · 2019-07-11 13:44:03Z

Please read about creating a MCVE. This is a stand-alone sample, which makes your issue reproducible, and helps us to provide easy answers...

If I got this correctly there is a 1:n related structure, where each node in your "object A" can have several nodes in "object B".

My following code will simulate this through INFORMATION_SCHEMA. Each table as 1 or many columns.

We solve this with a correlated sub-query. This is the way to create nested JSON arrays:

SELECT TOP 3 t.TABLE_NAME AS NodeName
            ,t.TABLE_TYPE AS NodeType
            ,(
                SELECT TOP 3 c.COLUMN_NAME AS ColumnName
                            ,c.DATA_TYPE AS ColumnType 
                FROM INFORMATION_SCHEMA.COLUMNS c 
                WHERE c.TABLE_CATALOG=t.TABLE_CATALOG
                 AND  c.TABLE_SCHEMA=t.TABLE_SCHEMA
                 AND  c.TABLE_NAME=t.TABLE_NAME
                FOR JSON PATH
             ) AS MyColumns
FROM INFORMATION_SCHEMA.TABLES t
FOR JSON PATH;

The result

[
    {
        "NodeName": "spt_fallback_db",
        "NodeType": "BASE TABLE",
        "MyColumns": [
            {
                "ColumnName": "xserver_name",
                "ColumnType": "varchar"
            },
            {
                "ColumnName": "xdttm_ins",
                "ColumnType": "datetime"
            },
            {
                "ColumnName": "xdttm_last_ins_upd",
                "ColumnType": "datetime"
            }
        ]
    },
    {
        "NodeName": "spt_fallback_dev",
        "NodeType": "BASE TABLE",
        "MyColumns": [
            {
                "ColumnName": "xserver_name",
                "ColumnType": "varchar"
            },
            {
                "ColumnName": "xdttm_ins",
                "ColumnType": "datetime"
            },
            {
                "ColumnName": "xdttm_last_ins_upd",
                "ColumnType": "datetime"
            }
        ]
    },
    {
        "NodeName": "spt_fallback_usg",
        "NodeType": "BASE TABLE",
        "MyColumns": [
            {
                "ColumnName": "xserver_name",
                "ColumnType": "varchar"
            },
            {
                "ColumnName": "xdttm_ins",
                "ColumnType": "datetime"
            },
            {
                "ColumnName": "xdttm_last_ins_upd",
                "ColumnType": "datetime"
            }
        ]
    }
]

As you can see, each table as a nested set of columns, represented through a JSON array.

Thanks, right perhaps there was some information missing in our main post. We have lots of split tables to split data. This was just a reconstructed minimalized example. We have 2 json objects that we want to merge, thats the main question. Each node could contain different data. Hence we have 2 different json objects that we want to merge into 1 using JSON_MODIFY

Collectives™ on Stack Overflow

TSQL JSON How to add an array to an existing Json Object?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related