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'

kgzdev · Accepted Answer · 2021-03-15 11:17:52Z

1

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

answered Mar 15, 2021 at 11:17

kgzdev

2,9312 gold badges21 silver badges37 bronze badges

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

Comments

Charlieface · Accepted Answer · 2021-03-15 12:24:43Z

1

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);

answered Mar 15, 2021 at 12:24

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Insert key-value into ALL objects of the JSON array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related