Insert key-value into ALL objects of the JSON array

Question

I have JSON array value column in database:

[
   {
      "id": 1,
      "name": "One"
   },
   {
      "id": 2,
      "name": "Two"
   }
]

I want to add a new key-value into each object. Expected result is:

[
   {
      "id": 1,
      "name": "One",
      "active": "1"
   },
   {
      "id": 2,
      "name": "Two",
      "active": "1"
   }
]

By using JSON_MODIFY I can add a new key-value into one of the object as following:

UPDATE MyTable
   SET JsonValueColumn=JSON_MODIFY(JsonValueColumn,'$[1].active','1')
 WHERE Id = 'id'

But I need to add a new key-value into all objects in JSON array. I have an option to iterate in json objects, add new key-value, combine them again and update JsonValueColumn with the new value.

But I am looking if there is a simpler way to achieve this. Something like:

UPDATE MyTable
   SET JsonValueColumn=JSON_MODIFY(JsonValueColumn,'$[*].active','1')
 WHERE Id = 'id'

score 1 · Answer 1 · answered Mar 15 '21 at 11:17

This is the simplest way I found so far:

UPDATE MyTable
SET JsonValueColumn= '[' + (
        SELECT
            STRING_AGG(JSON_MODIFY([value],'$.active', '1'), ',') WITHIN GROUP (ORDER BY CAST([key] AS int))
        FROM
            OPENJSON(JsonValueColumn)
    ) + ']'

From: https://stackoverflow.com/a/62648430/2794280

score 1 · Answer 2 · answered Mar 15 '21 at 12:24

You can use OPENJSON to break out the JSON objects, then CROSS APPLY the new value, then rebuild with FOR JSON:

UPDATE MyTable
SET JsonValueColumn = 
    (SELECT *
    FROM OPENJSON(@json) WITH (id int, name varchar(100))
    CROSS APPLY (VALUES(1)) v(active)
    FOR JSON PATH);

Insert key-value into ALL objects of the JSON array

2 Answers2