patternMinor
Add items to JSON
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?
Will give:
Where I would like to have:
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:
Notably, you must quote-wrap the
Sadly the original JSON is no longer prettified, but remains valid for JSON parsing.
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.