Adding array to array with JSON_MODIFY

Question

DECLARE @JSON_CurrentArray NVARCHAR(MAX) = '{"Some List":
[{"Name":"Item1","Id":"2"},{"Name":"Item2","Id":"3"}]}';
DECLARE @JSON_TopLevel NVARCHAR(MAX) = '{"OverAll":[{"Product Section":[]}]}';
SET @JSON_TopLevel = JSON_MODIFY(@JSON_TopLevel, 'append $."Overall"."Product Selection"', JSON_QUERY(@JSON_CurrentArray));
SELECT @JSON_TopLevel;

Ive been trying to stick CurrentArray into TopLevel,

Tried some crazy append/lax/strict combinations... but im new to JSON Manipulation and am almost at 'liquid brain stage' over this item. I also thought about adding a blank array, but to no avail (i might be doing that wrong also)

Right now im code blind, so, If you can somehow inject one array into another... #foreverindebted.

Lukasz Szozda · Accepted Answer · 2017-07-25 12:16:33Z

1

If I understand you correctly you want something like this:

DECLARE @JSON_CurrentArray NVARCHAR(MAX) = '{"Some List":
[{"Name":"Item1","Id":"2"},{"Name":"Item2","Id":"3"}]}';
DECLARE @JSON_TopLevel NVARCHAR(MAX) = '{"OverAll":[{"Product Section":[]}]}';
SET @JSON_TopLevel = JSON_MODIFY(@JSON_TopLevel, 'append $."OverAll"[0]."Product Section"', JSON_QUERY(@JSON_CurrentArray));
SELECT @JSON_TopLevel;

DBFiddle

Result:

{"OverAll":[{"Product Section":[{"Some List": [{"Name":"Item1","Id":"2"},{"Name":"Item2","Id":"3"}]}]}]}

To check if your path is correct you could use JSON_QUERY and strict mode:

SELECT JSON_QUERY(@JSON_TopLevel, 'strict $."OverAll"."Product Section"')
--Msg 13608 Level 16 State 5 Line 7
--Property cannot be found on the specified JSON path.

-- vs
SELECT JSON_QUERY(@JSON_TopLevel, 'strict $."OverAll"[0]."Product Section"')
-- []

edited Jul 25, 2017 at 12:16

answered Jul 25, 2017 at 12:11

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

That Data Architect Guy Over a year ago

Sorry about the product selection/section. Can't use my actual data. I'll be trying this in about an hour. But so I understand correctly... I'm building a JSON object with various depth. Would overall always have the position[0] or would I need to declare other attributes' position down the line. Assume that all of my multiple records with similar names are leaves and never need to be added to. I have a parent child tree, unique names, and I'm filling up TopLevel with data from the tree (to serve, not store/edit)

Collectives™ on Stack Overflow

Adding array to array with JSON_MODIFY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related