SQL JSON query to extract an object

user_10805883 0
Viewed 64

I think the problem is because I have hard brackets [] in the 'groups' object.

JSON:

declare @json nvarchar(max) = N'{ "agents": [ {
    "id": 9544321, 
    "uuid":"xxxx-xxx-xxxx",
    "groups": [
            {
                "name": "GROUP NAME HERE", 
                "id": 123456
            }
        ],
    "support": true
    }]}'

What I'm trying to get to is:

id uuid group name group id
9544321 xxxx-xxx-xxxx GROUP NAME HERE 123456

And then if there is a second group specified in the 'groups' object that'd be two rows. etc

This is my code so far, but it just leaves the [group name] and [group id] columns blank. I've also tried a cross apply and that didn't work (same result).

select * 
from openjson(@json, '$.agents') 
    with (
        id  int '$.id', 
        uuid varchar(60) '$.uuid', 
        groups nvarchar(max) '$.groups' as json     
    )

outer apply openjson (groups) 
    with ([group name] nvarchar(max) '$', 
    [group id] int '$'
    )

SQL output

tsql sql-server sql json
2 Answers

Try this:

    select * 
from openjson(@json, '$.agents') 
    with (
        id  int '$.id', 
        uuid varchar(60) '$.uuid', 
        groups nvarchar(max) '$.groups' as json     
    )

outer apply openjson (groups, '$') 
    with ([name] nvarchar(max), 
    [id] int
    )

Did you find the difference? You have placed the value 'Group Name' which is the value, instead of keys in json.

user_62032110

@BeckyG, you're almost there. Please, pay attention to lines 29 and 30:

DECLARE @json NVARCHAR(MAX) = N'{ "agents": [ {
    "id": 9544321, 
    "uuid":"xxxx-xxx-xxxx",
    "groups": [
            {
                "name": "GROUP NAME HERE", 
                "id": 123456
            },
            {
                "name": "GROUP NAME HERE 2", 
                "id": 1234567
            }
        ],
    "support": true
    }]}';

SELECT agents.id,
       agents.uuid,
       groups.name [group name],
       groups.id [group id]
FROM OPENJSON(@json, '$.agents') 
    WITH (
        id  INT '$.id', 
        uuid VARCHAR(60) '$.uuid', 
        groups NVARCHAR(MAX) '$.groups' AS JSON
    ) AS agents
OUTER APPLY OPENJSON (agents.groups) 
    WITH (
        [name] NVARCHAR(MAX) '$.name', 
        [id] INT '$.id'
    ) AS groups;

Happy coding!

user_13366510
Related