CODEWITHSUNDEEP
sqlarraysjsonsql-serversql-server-2019
janhase
janhase

Reputation: 21

Appending a new array to a JSON object with SQL Server

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

Upvotes: 2

Views: 1067

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272096

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"]}

Upvotes: 3

Related Questions