Modifying multiple JSON array elements in SQL Server 2017

Question

I have a SQL Server 2017 table Orders which has an OrderId primary key and nvarchar(max) column Details. This column contains a json string which represents an array of "items". Here is a sample:

{ items[
{
    "id": 1,
    "isDeleted": false
},
{
    "id": 2,
    "isDeleted": false
},
{
    "id": 3,
    "isDeleted": false
},
{
    "id": 4,
    "isDeleted": false
}
] }

I am trying to figure out if there is a way to have a single (or few) SQL statement which will allow me to update one or more of the isDeleted attributes in the Details column of this table, given an OrderId for the record in the table and also a list of Ids in the Details column to update.

So for instance, I would like to update Ids 2 and 3 to be true in the Details JSON string record for a given OrderId. I know I can do this in a while loop and using json_modify, but I am wondering if there is a more elegant solution with some combination of json_modify, json_query or openjson.

Thanks in advance for any suggestions.

I am trying to change the isDeleted value to true for certain ids. — shenk
– shenk, Commented May 7, 2020 at 12:35

Zhorov · Accepted Answer · 2020-05-08 06:30:01Z

6

You may use one of the following approaches:

Parse the Details JSON for each OrderId uisng OPENJSON() and explicit schema. The result is a table with columns, defined in the WITH clause. Update this table and return the changed data as JSON again using FOR JSON.
Parse the Details JSON for each OrderId uisng OPENJSON() and default schema. The result is a table with columns key, value and type and one row for each item (JSON object) in the items JSON array. Update this table and generate the items JSON array with string-based approach (I don't think that FOR JSON can generate an array of scalar values / JSON objects). Update the JSON in the source table with JSON_MODIFY().
Generate and execute a dynamic statement using JSON_MODIFY()

Table with data:

CREATE TABLE Orders (OrderId int, Details nvarchar(max))
INSERT INTO Orders (OrderId, Details)
VALUES 
   (1, N'{"items":[{"id":1,"isDeleted":false},{"id":2,"isDeleted":false},{"id":3,"isDeleted":false},{"id":4,"isDeleted":false}]}'),
   (2, N'{"items":[{"id":11,"isDeleted":false},{"id":12,"isDeleted":false},{"id":13,"isDeleted":false}]}')

Table with IDs:

CREATE TABLE ItemIds (id int)
INSERT INTO ItemIds (id) VALUES (1), (3)

Statement with OPENJSON() and explicit schema:

UPDATE Orders
SET Details = (
   SELECT 
      j.id AS id, 
      CONVERT(bit, CASE WHEN i.id IS NOT NULL THEN 1 ELSE j.isDeleted END) AS isDeleted
   FROM OPENJSON(Details, '$.items') WITH (
      id int '$.id', 
      isDeleted bit '$.isDeleted'
   ) j
   LEFT OUTER JOIN ItemIds i ON j.id = i.id
   FOR JSON AUTO, ROOT('Items')
)
WHERE OrderId = 1

Statement with OPENJSON() and default schema:

UPDATE Orders
SET Details = JSON_MODIFY(
   Details,
   '$.items',
   JSON_QUERY((
      SELECT CONCAT(
         '[', 
         STRING_AGG(
            CASE 
               WHEN i.id IS NULL THEN j.[value] 
               ELSE JSON_MODIFY(j.[value], '$.isDeleted', CONVERT(bit, 1)) 
            END,
            ','
         ),
         ']'
      )   
      FROM OPENJSON(Details, '$.items') j
      LEFT OUTER JOIN ItemIds i ON CONVERT(int, JSON_VALUE(j.[value], '$.id')) = i.id
   ))
)
WHERE OrderId = 1

Dynamic statement:

DECLARE @stm nvarchar(max) 
SELECT @stm = STRING_AGG(
    CONCAT(
      'UPDATE Orders ',
      'SET Details = JSON_MODIFY(Details, ''$.items[', a.[key], '].isDeleted'', CONVERT(bit, 1)) ',
      'WHERE OrderId = ', o.OrderId, ';'
   ),
   ' '
)   
FROM Orders o
CROSS APPLY (
   SELECT o.OrderId, j1.[key]
   FROM OPENJSON(o.Details, '$.items') j1
   CROSS APPLY OPENJSON(j1.[value]) WITH (id int '$.id') j2
   WHERE j2.id IN (SELECT id FROM ItemIds)
) a   
WHERE o.OrderId = 1

PRINT @stm 
EXEC sp_executesql @stm

Result:

OrderId Details
1   {"items":[{"id":1,"isDeleted":true},{"id":2,"isDeleted":false},{"id":3,"isDeleted":true},{"id":4,"isDeleted":false}]}
2   {"items":[{"id":11,"isDeleted":false},{"id":12,"isDeleted":false},{"id":13,"isDeleted":false}]}

edited May 8, 2020 at 6:30

answered May 7, 2020 at 13:26

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

5 Comments

shenk Over a year ago

The Dynamic statement method worked like a charm. I feel kinda silly not thinking of dynamic sql as a possible solution. I had some issues with the other method you presented. One piece of info I didn't present in my question (I apologize for the ommission) is that the json schema for the Details field is more complex than just "id" and "isDeleted". I left out other elements for brevity. It looks like your first suggestion would basically remove all other elements in the json. Is there a way to modify your first suggestion to just update the isDeleted element without touching the others?

Zhorov Over a year ago

@shenk You need to include all the keys from the Details JSON in the schema definition (the WITH clause) and in the SELECT ... FOR JSON statement.

shenk Over a year ago

That's sort of what i figured. I don't have full control over that schema, so I will go with the dynamic sql approach. Thanks for your suggestions!

Zhorov Over a year ago

@shenk The answer is updated with an additional approach.

shenk Over a year ago

Wow, even better. Very slick. Thanks for your continued help on this!

Lukasz Szozda · Accepted Answer · 2020-05-05 19:53:01Z

SQL Server is perfectly capable of performing such operation. It is another question if this is good design though.

This is just a demo and not production ready code, so there is a lot of space for improvement:

-- param section
DECLARE @OrderId INT = 1;

DECLARE @t TABLE(id INT, new_val NVARCHAR(10));
INSERT INTO @t(id, new_val) VALUES(1, 'true'),(3, 'true');



--- single query
WITH cte AS (
 SELECT o.*, 
  s.[key], 
  JSON_VALUE(s.value, '$.id') AS id,
  JSON_VALUE(s.value, '$.isDeleted') AS isDeleted
 FROM Orders o
 CROSS APPLY OPENJSON(o.Details ,N'$.items') s
 WHERE o.OrderId = @OrderId
), cte_new AS (
SELECT DISTINCT c.OrderId, c.Details, s.Details_new
FROM cte c
CROSS APPLY (
  SELECT  c2.id, isDeleted = COALESCE(t.new_val, c2.IsDeleted)
  FROM cte c2
  LEFT JOIN @t t
    ON c2.id = t.id
  WHERE c2.OrderId = c.OrderId
  FOR JSON AUTO) s(Details_new)
)
UPDATE o
SET Details = cn.Details_new
FROM Orders o
JOIN cte_new cn
  ON o.OrderId = cn.OrderId;

db<>fiddle demo

How it works:

Parse JSON to tabular format
Perform data manipulation(here using @t as parameter)
Aggregate back to JSON
Perform UPDATE

ITHelpGuy · Accepted Answer · 2020-05-05 21:24:51Z

0

I don't have the right version of SQL Server to test out this code. But, you should be able to query and modify the data and generate a new json string.

DECLARE @json nvarchar(max) = '{"items" : [{"id": 1, "isDeleted": false}, {"id": 2, "isDeleted": false}, {"id": 3, "isDeleted": false}, {"id": 4, "isDeleted": false}]}'

SELECT *
  FROM OPENJSON(@json)
  WITH (id int '$.items.id', isDeleted bit '$.items.isDeleted')

answered May 5, 2020 at 21:24

ITHelpGuy

1,0472 gold badges19 silver badges39 bronze badges

Collectives™ on Stack Overflow

Modifying multiple JSON array elements in SQL Server 2017

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related