SQL Server – Best Way to Unpack JSON Objects

azure-sql-databasejson

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": "WMogh@starfleet.gov",
    "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": "WMogh@starfleet.gov",
    "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?

Best Answer

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": "WMogh@starfleet.gov",
        "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": "WMogh@starfleet.gov",
        "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

Related Question