How to I return multiple rows from a nested JSON object stored in a table

Question

Given the following JSON stored in a nvarchar(max) column, how to generate the table shown? I can make is for if the nested object is an [], an array, but not a structure {}

DECLARE @JSON AS NVARCHAR(MAX);

SET  @JSON = N'{
    "ACCOUNT": 1
,   "USERS" : {
        "BOB" : 1
    ,   "JOHN" : 2
    ,   "RODGER" : 3
    }
}';


SELECT JSON_QUERY(@JSON,'$.USERS')


ACCOUNT   USER     USER_VALUE
================================
    1     BOB          1
    1     JOHN         2
    1     RODGER       3

Why do you want to do this?. It doesn't seem like something that SQL is suited to. — Peter Smith
– Peter Smith, Commented Feb 16, 2019 at 8:16

Zhorov · Accepted Answer · 2019-02-17 13:43:21Z

2

You may try with the next approach using OPENJSON (with an explicit structure for ACCOUNT and USERS columns) and additional CROSS APPLY:

-- JSON
DECLARE @JSON AS NVARCHAR(MAX);
SET  @JSON = N'{
    "ACCOUNT": 1
,   "USERS" : {
        "BOB" : 1
    ,   "JOHN" : 2
    ,   "RODGER" : 3
    }
}';

-- Statement
SELECT i.ACCOUNT, v.[key] AS [USER], v.[value] AS USER_VALUE
FROM OPENJSON(@json) 
WITH (
   ACCOUNT int '$.ACCOUNT',
   USERS nvarchar(max) '$.USERS' AS JSON
) i
CROSS APPLY (
   SELECT *
   FROM OPENJSON(i.USERS)
) v

Output:

ACCOUNT USER    USER_VALUE
1       BOB     1
1       JOHN    2
1       RODGER  3

edited Feb 17, 2019 at 13:43

answered Feb 16, 2019 at 8:18

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to I return multiple rows from a nested JSON object stored in a table

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related