SQL Server - Change JSON string value to array-CodePudding

I have a SQL Server Table with a JSON column. In it, I have a property 'Code' that contained a string with one value. Now, I want that 'Code' to be an Array to be able to contain more than one strings.

How can I update all my table values to change the property to an array?

"Code" : null --> "Code" : []
"Code" : "XX" --> "Code" : ["XX"]

CodePudding user response：

You may try to modify the stored JSON as text using OPENJSON() with default schema to get the type of the $.Code part:

Sample data:

SELECT *
INTO Data
FROM (VALUES
   (CONVERT(nvarchar(max), N'{"Code": "XX"}')),
   (CONVERT(nvarchar(max), N'{"Code": null}')),
   (CONVERT(nvarchar(max), N'{"Code": 1}')),
   (CONVERT(nvarchar(max), N'{"Code": []}')),
   (CONVERT(nvarchar(max), N'{"Code": {}}'))
) d (JsonColumn)

Statement:

UPDATE d
SET JsonColumn = JSON_MODIFY(
   JsonColumn, 
   '$.Code', 
   JSON_QUERY(CONCAT('[', j.[value], ']'))
)
FROM Data d
OUTER APPLY (
   SELECT 
      CASE 
         WHEN [type] = 0 THEN ''
         WHEN [type] = 1 THEN CONCAT('"', STRING_ESCAPE([value], 'json'), '"')
         WHEN [type] = 2 THEN [value]
         ELSE '"not a scalar value"'
      END AS [value]
   FROM OPENJSON(d.JsonColumn, '$')
   WHERE [key] = 'Code'
) j

Result:

JsonColumn
--------------------------------
{"Code": ["XX"]}
{"Code": []}
{"Code": [1]}
{"Code": ["not a scalar value"]}
{"Code": ["not a scalar value"]}

You may consider the @JeroenMostert's comment and use something like this:

UPDATE d
SET JsonColumn = JSON_MODIFY(
   JsonColumn, 
   '$.Code', 
   JSON_QUERY(CONCAT('[', j.[value], ']'))
)
FROM Data d
OUTER APPLY (
   SELECT IIF ([type] = 1, CONCAT('"', STRING_ESCAPE([value], 'json'), '"'), [value]) AS [value]
   FROM OPENJSON(d.JsonColumn, '$')
   WHERE [key] = 'Code'
) j