How to update insert JSON property in an array of object with T-SQL

Question

I am trying to find an effective way to insert a property into an existing json array with out having to do it by index. For example say we had:

DECLARE @json NVARCHAR(MAX);
SET @json = N'
  {
    "objs":[
       {"id":1},
       {"id":2}
     ]
  }
'

How do I add a property to each object in the array? What I would like to do is something like this:

JSON_MODIFY(@json,'$.objs[].parent_id',1);

But this does not work because I did not provide an array index. I am sure there is a simple solution to this, but I could not find one in the docs.

Zhorov · Accepted Answer · 2019-12-11 20:01:42Z

4

If you use SQL Server 2017+, you may use JSON_MODIFY() with an expression as path, as is explained in the documentation:

In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path.

JSON:

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
   "objs":[
      {
         "id":1
      },
      {
         "id":2
      }
   ]
}';

Statement:

SELECT @json = JSON_MODIFY(@json, CONCAT('$.objs[', [key], '].parent_id'), 1)
FROM OPENJSON(@json, '$.objs')

Result:

{
   "objs":[
      {
         "id":1,
         "parent_id":1
      },
      {
         "id":2,
         "parent_id":1
      }
   ]
}

edited Dec 11, 2019 at 20:01

answered Dec 11, 2019 at 19:53

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

2 Comments

rahicks Over a year ago

This is exactly what I was looking for Thanks!

Caltor Over a year ago

Link to JSON_MODIFY() in the docs learn.microsoft.com/en-us/sql/t-sql/functions/…

Salman Arshad · Accepted Answer · 2019-12-11 18:52:40Z

1

I suppose you can use OPENJSON and FOR JSON to shred and combine the JSON:

DECLARE @json NVARCHAR(MAX);

SET @json = N'{
  "objs": [{
    "id": 1
  }, {
    "id": 2
  }]
}';

SELECT id, 1 AS parent_id
FROM OPENJSON(@json, '$.objs')
WITH (
    id INT '$.id'
)
FOR JSON PATH, ROOT('objs')

Result:

{
  "objs": [{
    "id": 1,
    "parent_id": 1
  }, {
    "id": 2,
    "parent_id": 1
  }]
}

answered Dec 11, 2019 at 18:52

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

2 Comments

rahicks Over a year ago

Yeah this is what I was bout to try. I was just hoping for a better way. Thanks!

Salman Arshad Over a year ago

JSON_MODIFY wont help since it modifies one property at a time. This solution should be OK unless you don't know the name of keys in child object in advance.

Collectives™ on Stack Overflow

How to update insert JSON property in an array of object with T-SQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related