2

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 A
  • 262,204
  • 82
  • 430
  • 521
janhase
  • 21
  • 2

1 Answers1

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"]}
Salman A
  • 262,204
  • 82
  • 430
  • 521