Edit json with array of objects error, it's replacing the array with one object

user_10597750 0
Viewed 91

I have a JSON column like this

[
  {
    "JoinedTime": "2021-04-13T20:09:40.654Z",
    "LeftTime": "2021-04-13T20:09:53.368Z",
  },
  {
    "JoinedTime": "2021-04-13T20:09:40.654Z",
    "LeftTime": null,
  },
]

And I have to update all null 'LeftTime' properties to GETUTCDATE(), so change that one 'null' value to the current GETUTCDATE().

I've gotten this far

UPDATE JoinedLeft
SET JsonColumn = JSON_MODIFY(js.[value], '$.LeftTime', FORMAT(GETUTCDATE(), 'yyyy-MM-dd"T"HH:mm:ss"Z"'))  
FROM JoinedLeft JL
CROSS APPLY OPENJSON(JL.JsonColumn) AS js
WHERE 
    JSON_VALUE(js.[value], '$.LeftTime') IS NULL AND
    JSON_VALUE(js.[value], '$.JoinedTime') IS NOT NULL

But it just replaces the column with just the object that I wanted to edit, instead of editing the object and saving the array again.

Can someone help me?

tsql sql-server sql json
1 Answers

When you parse a JSON array with OPENJSON() and default schema (without the WITH clause), the result is a table with rows for each item in the parsed JSON array. This explains the enexpected results from the UPDATE statement. I don't think that JSON_MODIFY() supports wildcards as path parameter, so one possible option is to parse, modify and build the JSON array again:

Table:

SELECT JsonColumn
INTO JoinedLeft
FROM (VALUES
   ('[
      {"JoinedTime": "2021-04-13T20:09:40.654Z","LeftTime": "2021-04-13T20:09:53.368Z"},
      {"JoinedTime": "2021-04-14T21:09:40.654Z", "LeftTime": null}
   ]'),
   ('[
      {"JoinedTime": "2021-05-14T21:09:40.654Z", "LeftTime": null}
   ]')
) t (JsonColumn)

Statement:

UPDATE JL
SET JL.JsonColumn = V.JsonColumn
FROM JoinedLeft JL
CROSS APPLY (
    SELECT 
       JoinedTime, 
       COALESCE(LeftTime, FORMAT(GETUTCDATE(), 'yyyy-MM-dd"T"HH:mm:ss"Z"')) AS LeftTime
    FROM OPENJSON(JL.JsonColumn, '$') WITH (
       JoinedTime varchar(24) '$.JoinedTime',
       LeftTime varchar(24) '$.LeftTime'
    ) AS JsonColumn
    FOR JSON PATH
) V (JsonColumn);

Result:

JsonColumn
[{"JoinedTime":"2021-04-13T20:09:40.654Z","LeftTime":"2021-04-13T20:09:53.368Z"},{"JoinedTime":"2021-04-14T21:09:40.654Z","LeftTime":"2021-05-18T12:12:35Z"}]
[{"JoinedTime":"2021-05-14T21:09:40.654Z","LeftTime":"2021-05-18T12:12:35Z"}]
user_65780800
Related