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.

Answer 1

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}]}

Answer 2

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

Answer 3

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:

1. Parse JSON to tabular format

2. Perform data manipulation(here using @t as parameter)

3. Aggregate back to JSON

4. Perform UPDATE