Update/Add attribute to json array in T-SQL

Question

All of the examples examples I see dealing with json arrays have the array nested under a top level object. I have a json array in a column: [{"key": "value1"}, {"key": "value2"}]

I would like to run a sql script to add/update a key for each element in the array, resulting in: [{"key": "value1", "otherKey": "otherValue"}, {"key": "value", "otherKey": "otherValue"}]

Yes, in my case I want the same value set for each array member. I've tried:

declare @info nvarchar(max)
SET @info  = '[{"key": "value1"}, {"key": "value2"}]'
print JSON_MODIFY(@info, '[0].otherKey', '""')

and fails with "JSON path is not properly formatted. Unexpected character '[' is found at position 0."

This is in MSSQL 2017.

Zhorov · Accepted Answer · 2020-06-30 20:26:23Z

The approach, that can be used, depends on JSON structure (I assume, that the count of the items in the JSON array is not fixed):

If the input JSON array has items (JSON objects) with fixed key/keys, you may use a combination of OPENJSON() with explicit schema (to parse this JSON as table) and FOR JSON (to modify and return the rows as JSON)
If the input JSON has items with different structure, you may use a combination of OPENJSON() with default schema, JSON_MODIFY() and STRING_AGG().

JSON:

declare @info nvarchar(max)
SET @info  = '[{"key": "value1"}, {"key": "value2"}]'

Statement for fixed structure:

SELECT @info = (
   SELECT [key], 'OtherValue' AS OtherKey
   FROM OPENJSON(@info) WITH ([key] varchar(100) '$.key')
   FOR JSON PATH
)

Statement for variable structure:

SELECT @info = CONCAT('[', STRING_AGG(JSON_MODIFY([value], '$.OtherKey', 'OtherValue'), ','), ']')
FROM OPENJSON(@info)
SELECT @info

Result:

[{"key":"value1","OtherKey":"OtherValue"},{"key":"value2","OtherKey":"OtherValue"}]

Note, that the reason for the error is that the value of path parameter ([0].otherKey) is wrong. The correct path expression is $[0].otherKey for the first item in the JSON array.

Preben Huybrechts · Accepted Answer · 2020-06-30 17:56:22Z

1

Have you tried adding $ before the indexer sign? Like:

declare @info nvarchar(max)
SET @info  = '[{"key": "value1"}, {"key": "value2"}]'
SELECT JSON_MODIFY(@info, '$[0].otherKey', '""')

This gives the following output:

[{"key": "value1","otherKey":"\"\""}, {"key": "value2"}]

answered Jun 30, 2020 at 17:56

Preben Huybrechts

6,1972 gold badges30 silver badges66 bronze badges

Collectives™ on Stack Overflow

Update/Add attribute to json array in T-SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related