What is the best way to unpack JSON objects in SQL Server?

Question

Part 2 of my 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"}]
 }' 

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?

Answer 1

I think your JSON definition for arrays might be a little odd, you should be able to unpack an array like so:

DECLARE @json VARCHAR( 4000 ) = '
 {
 "Email": "[email protected]",
 "Prefix":null,
 "FirstName": "Worf",
 "MiddleInitial": "",
 "LastName": "Mogh",
 "Suffix": "Son Of",
 "Title" :"Commander",
 "Groups": ["0", "1", "5"]
 }';
SELECT ISJSON( @json );
SELECT j.value
FROM OPENJSON( @json, 'strict $.Groups' ) j;

value

0

1

5

The notation "Groups":[{"0", "1", "5"}] is saying that within an object, there is a key "Groups" which has a value of an array containing a single element. That element is also an object, but then - "0", "1", "5" is not meaningful in a JSON context. For instance:

DECLARE @json VARCHAR( 4000 ) = '
 {
 "Email": "[email protected]",
 "Prefix":null,
 "FirstName": "Worf",
 "MiddleInitial": "",
 "LastName": "Mogh",
 "Suffix": "Son Of",
 "Title" :"Commander",
 "Groups": [{"A": "0", "B": "1", "C": "5"}]
 }';
SELECT ISJSON( @json );
SELECT A = JSON_VALUE( j.value, '$.A' ),
 B = JSON_VALUE( j.value, '$.B' ),
 C = JSON_VALUE( j.value, '$.C' ) 
FROM OPENJSON( @json, 'strict $.Groups' ) j

A B C

0 1 5