Appending a new array to a JSON object with SQL Server

Question

I would like to append an array to a JSON object:

JSON object:

{ "value1": "test" }

Array to be appended:

{ "array2": ["1", "2", "3"] }

Expected result:

{ "value1": "test", "array2": ["1", "2", "3"] }

My attempts with JSON_MODIFY failed:

Attempt #1:

SELECT 
    JSON_MODIFY('{ "value1": "test" }',
                'append $.array2', 
                JSON_QUERY('[ "1", "2", "3" ]'))

-- { "value1": "test", "array2": [["1", "2", "3"]] }
-- An array within an array is appended

Attempt #2:

SELECT 
    JSON_MODIFY('{ "value1": "test" }',
                'append $',
                JSON_QUERY('{"array2": [ "1", "2", "3" ]}'))

-- { "value1": "test" }
-- Result doesn't contain the array at all

Salman Arshad · Accepted Answer · 2021-09-27 11:40:16Z

3

You are trying to set a property, not appending items to array so remove the append keyword:

SELECT JSON_MODIFY(
    '{"value1": "test"}',
    '$.array2',
    JSON_QUERY('["1", "2", "3"]')
)
-- {"value1": "test","array2":["1", "2", "3"]}

answered Sep 27, 2021 at 11:40

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

Collectives™ on Stack Overflow

Appending a new array to a JSON object with SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related