0

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?

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": "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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.