sql-server OPENJSON ways to parse multiple array value elements as one element?-CodePudding

Is there is any other (more pretty) way to accomplish the following?

I am struggling with a way to display multiple elements of an array as one element.

I have a JSON-string, that looks like this:

DECLARE @JSON nvarchar(max)
SELECT @JSON = N'{"firstNames": ["Bengt","Erik"]}'

I would like it to be formatted like this:

| firstNames |
| ---------- |
| Bengt Erik |

This approach:

SELECT value as firstNames FROM OPENJSON (@JSON, '$.firstNames')

results in

| firstNames |
| ---------- |
| Bengt      |
| Erik       |

So I tried:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(value,',',' '),'"',''),'[',''),']','') as firstNames 
FROM OPENJSON (@JSON, '$')

And yes, it does give me the result I want, but can this be done in another way?

CodePudding user response：

You could aggregate the First Names after you receive them:

SELECT STRING_AGG(value,' ') WITHIN GROUP (ORDER BY [key]) as firstNames
FROM OPENJSON (@JSON, '$.firstNames');