SQL Server - Change JSON string value to array

Question

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

Assuming every object currently contains only scalar values for Code, UPDATE MyTable SET jsonColumn = JSON_MODIFY(jsonColumn, '$.Code', CONCAT('[', JSON_VALUE(jsonColumn, '$.Code'), ']')). If we cannot assume this things get much trickier. — Jeroen Mostert
– Jeroen Mostert, Commented Mar 10, 2022 at 15:29
@JeroenMostert, that would work with int, but no with strings, as it loses the " "Code" : "XX" would be changed to "Code" : [XX] and I want it to be "Code" : ["XX"] — popiandro
– popiandro, Commented Mar 10, 2022 at 15:35
Oh, right. Yeah, that's quite a bit more complicated, since we have to get JSON_QUERY and/or OPENJSON involved. Oh well, that's why it's not an answer. :P — Jeroen Mostert
– Jeroen Mostert, Commented Mar 10, 2022 at 15:36

Zhorov · Accepted Answer · 2022-03-10 16:08:23Z

3

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

edited Mar 10, 2022 at 16:08

answered Mar 10, 2022 at 15:51

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

4 Comments

Jeroen Mostert Over a year ago

This can be made a bit shorter by using the CASE [type] WHEN... syntax (same semantics, of course). Also, don't forget to STRING_ESCAPE([value], 'json') in the case of strings.

Zhorov Over a year ago

@JeroenMostert, thanks, STRING_ESCAPE() is very important.

Jeroen Mostert Over a year ago

The "not a scalar value" thing seems unwise (and unnecessary); simply interpolating value again should usually work. In fact, IIF ([type] = 1, CONCAT('"', STRING_ESCAPE([value], 'json'), '"'), [value]) should cover everything, since really only strings are special.

Jeroen Mostert Over a year ago

Yeah, mine is still going on or I would have posted an answer myself. :P

Collectives™ on Stack Overflow

SQL Server - Change JSON string value to array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related