HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Add items to JSON

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
jsonitemsadd

Problem

I have the following JSONs which I would like to combine. In my JSON I have the part 'kenmerken' where I would like to add some more items to. I thought this could be done by using the JSON_MODIFY.
However when I use the statement below, it will replace the item 1, 2 and 3. And when I include 'append' before $.kenmerken, it will only show the new ones. How can I achieve to have them both in the kenmerken object?

DECLARE @FirstJSON NVARCHAR(MAX) = N'
{
    "message id": "B673A8E4-3652-4544-A02D-BA9726BD71ED",
    "volgnummer": 61000233530024,
    "debug": null,
    "kenmerken": {
        "item 1": 1,
        "item 2": 1,
        "item 3": 1
    }
}
';

DECLARE @KenmerkenToAdd NVARCHAR(MAX) = N'
{
    "Item 4": false,
    "Item 5": false
}
';

SET @FirstJSON = JSON_MODIFY(
                               @FirstJSON,
                               ' $.kenmerken',
                               JSON_QUERY(@KenmerkenToAdd)
                            );

SELECT @FirstJSON;


Will give:

{
"message id": "B673A8E4-3652-4544-A02D-BA9726BD71ED",
"volgnummer": 61000233530024,
"debug": null,
"kenmerken": {
   "Item 4": false,
   "Item 5": false
   }
}


Where I would like to have:

{
    "message id": "B673A8E4-3652-4544-A02D-BA9726BD71ED",
    "volgnummer": 61000233530024,
    "debug": null,
    "kenmerken": {
       "Item 1": 1,
       "Item 2": 1,
       "Item 3": 1,
       "Item 4": false,
       "Item 5": false
       }
    }

Solution

This is mostly answered already on StackOverflow. However, modifications for your specific use case are needed. Complete fiddle here, but the gist of it is:
DECLARE @Kenmerken NVARCHAR(MAX) = JSON_QUERY(@FirstJSON,' $.kenmerken');

SELECT @Kenmerken = JSON_MODIFY(
@Kenmerken,
CONCAT(N'$.', QUOTENAME([key],'"')),
[value]
)
FROM OPENJSON(@KenmerkenToAdd);

SET @FirstJSON = JSON_MODIFY(
@FirstJSON,
' $.kenmerken',
JSON_QUERY(@Kenmerken)
)
;


Notably, you must quote-wrap the key names because of the space character in them. Please note, any keys in @KenmerkenToAdd that are already present in @Kenmerken will overwrite the original values with this approach (although this is consistent with the last-value-wins duplicate key rules in most JSON specs).

Sadly the original JSON is no longer prettified, but remains valid for JSON parsing.

Context

StackExchange Database Administrators Q#332047, answer score: 3

Revisions (0)

No revisions yet.