Concatenate Array Values Using Json_Query

Question

I have a table in a SQL Server database that stores JSON in one of its columns. The structure is as follows:

Table Person

| Name   |      ExtraInfo                             |
|--------|:------------------------------------------:|
| Bob    |  {"Age":"23","Colors":["Red","Blue"]}      |
| James  |  {"Age":"26","Colors":["Green","Yellow"]}  |

If I run this query:

select
Json_value(ExtraInfo,'$.Age') as Age,
json_query(ExtraInfo, '$.Colors') as Colors
from Persons

I will get something like this:

| Age |Colors              |
|-----|:-------------------|
| 23  |  ["Red","Blue"]    |
| 26  |  ["Green","Yellow"]|

However I would need transform the Colors property of the JSON array into a nvarchar column with all the values of the array concatenated by a space character like this:

| Age | Colors       |
|-----|:-------------|
| 23  | Red Blue     |
| 26  | Green Yellow |

Is there any way that I can compose multiple calls to json_query or some other similar approach to accomplish this in a single SQL query?

score 3 · Accepted Answer · answered May 28 '20 at 01:34

One option is to use a CROSS APPLY and string_agg()

Example

Declare @YourTable Table ([Name] varchar(50),[ExtraInfo] varchar(50))  Insert Into @YourTable Values 
 ('Bob','{"Age":"23","Colors":["Red","Blue"]}')
,('James','{"Age":"26","Colors":["Green","Yellow"]}')


Select Json_value(ExtraInfo,'$.Age') as Age
      ,B.*
 From @YourTable A
 Cross Apply (
                Select Colors = STRING_AGG( value ,' ')
                 From  OpenJSON(json_query(ExtraInfo, '$.Colors'),'$')
             ) B

Returns

Age Colors
23  Red Blue
26  Green Yellow

Concatenate Array Values Using Json_Query

1 Answers1