CODEWITHSUNDEEP
jsonazure-sql-database
James
James

Reputation: 386

How to properly unpack a JSON array in SQL Server

I'm making a foray into JSON, I'd like to add a user to multiple groups: insert an JSON array into a table.

Ideally, the JSON would look like this:

'{
"Email": "[email protected]",
"Prefix":null,
"FirstName": "Worf",
"MiddleInitial": "",
"LastName": "Mogh",
"Suffix": "Son Of",
"Title" :"Commander",
"Groups": [{"0", "1", "5"}]
"Better_Groups": [{"ID":"0", "ID":"1", "ID":"5"}]
}'

Currently, I can do it with JSON like this:

'{
"Email": "[email protected]",
"Prefix":null,
"FirstName": "Worf",
"MiddleInitial": "",
"LastName": "Mogh",
"Suffix": "Son Of",
"Title" :"Commander",
"Groups": "1,2,3,4"
}'

then "unpack" it with the following ditty:

declare @groups varchar(1000)

select @groups = Groups from openjson(@json)
                WITH
        (

        Groups nvarchar(100)        '$.Groups'
        )

        print @groups

select value from string_split(@groups, ',')

which returns a nice little table like so:

Value
1
2
3
4

Problem This is bad JSON and the Web developer will make fun of me.

Question How do you propely unpack a JSON array in SQL Server?

update:

The final JSON used looks like so:

    @json =
            '{
            "Email": "[email protected]",
            "Prefix":null,
            "FirstName": "Worf",
            "MiddleInitial": "",
            "LastName": "Mogh",
            "Suffix": "Son Of",
            "Title" :"Commander",
            "Groups": "1,2,3,4",
            "Better_Groups": ["0", "1", "5"]
            }'

Upvotes: 1

Views: 948

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175944

Assuming that "groups" element is an array:

DECLARE @json NVARCHAR(MAX) = 
N'{
"Email": "[email protected]",
"Prefix":null,
"FirstName": "Worf",
"MiddleInitial": "",
"LastName": "Mogh",
"Suffix": "Son Of",
"Title" :"Commander",
"Better_Groups": ["0", "1", "5"]
}';

SELECT s.value
FROM OPENJSON(JSON_QUERY(@json, '$.Better_Groups')) s;

db<>fiddle demo

Upvotes: 1

Related Questions