How to replace complete object within JSON Object in Sql server

Question

I am trying to modify existing json in sql and adding a new property in each object in array.

My json is as given below

{"Key":"Employee", "Data":[{"id": "1", "value": "Vogel"},{ "id": "2", "value": "Vogel"}]

Now I am trying to add a new property in each object inside Data property(Like IsDeleted :False).

Can we iterate each object inside a particular property which is an array in itself and add property in each object.

Zhorov · Accepted Answer · 2018-12-28 13:15:45Z

4

One possible approach is to convert $.Data part of your JSON string to table with OPENJSON(), SELECT rows with new isDeleted column as JSON and modify original JSON:

DECLARE @json nvarchar(max)
SET @json = N'
    {
    "Key": "Employee", 
    "Data": [
        {"id": "1", "value": "Vogel"},
        {"id": "2", "value": "Vogel"}
    ]
    }';

SELECT @json = JSON_MODIFY(
   @json,
   '$.Data',
   (
   SELECT items.[id], items.[value], 'False' AS [isDeleted]
   FROM OPENJSON(JSON_QUERY(@json, '$.Data')) 
   WITH (
      [id] nvarchar(10),
      [value] nvarchar(100)  
   ) AS items
   FOR JSON PATH
   )
)

Output:

{
    "Key": "Employee", 
    "Data": [{"id":"1","value":"Vogel","isDeleted":"False"},{"id":"2","value":"Vogel","isDeleted":"False"}]
}

edited Dec 28, 2018 at 13:15

answered Dec 28, 2018 at 12:14

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

How to replace complete object within JSON Object in Sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related