CODEWITHSUNDEEP
sqljsonsql-servert-sql
George Mauer
George Mauer

Reputation: 122202

How to serialize to json array?

I would like to serialize the following to a simple array of strings not of objects. 

DECLARE @json NVARCHAR(MAX) = '["Developer", "Designer"]'

SELECT x.position
FROM (
    SELECT position
    FROM OPENJSON(@json)
    WITH (position nvarchar(50) '$')
) x
FOR JSON PATH

When I run the above I get: [{position: "Developer"}, {position: "Designer}]

But I want: ["Developer", "Designer"]

How would I do that?

Upvotes: 0

Views: 469

Answers (2)

Nathan-NWCC
Nathan-NWCC

Reputation: 49

Try declaring your json like this...

DECLARE @json NVARCHAR(MAX) = '{{"position": "Developer"}, {"position": "Designer"}}'

hth,

-n

Upvotes: -1

Piotr Palka
Piotr Palka

Reputation: 3169

I don't think you can do it with "FOR JSON".
So back to string operations:

DECLARE @json NVARCHAR(MAX) = '["Developer", "Designer"]'

SELECT '["'+STRING_AGG( STRING_ESCAPE(x.position, 'json'),'", "') + '"]'
FROM (
    SELECT position 
    FROM OPENJSON(@json)
    WITH (position nvarchar(50) '$')
) x

Upvotes: 2

Related Questions