CODEWITHSUNDEEP
jsonsql-servert-sqlsql-server-2016
Ram
Ram

Reputation: 825

parsing json values in sql server

I want to parse the below json values.

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[{"ID":1,"Roles":[1,2]},{"ID":2"Roles":[1,2,3]},{"ID":3,"Roles":[1,2,3,4]}]'

i want to display below output

Required Output
ID ROLES
1  1
1  2
2  1
2  2
2  3
3  1
3  2
3  3
3  4

I want query for this.

Upvotes: 1

Views: 354

Answers (2)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

An OPENJSON with a WITH unrolls the outer layer, then a regular one can be used to unroll the arrays. (The alias isn't strictly necessary, I just think it's clearer, and it becomes necessary if you have to peel even more layers.)

SELECT ID, R.[Value] AS [Role]
FROM OPENJSON(@json) WITH (
    ID INT, 
    [Roles] NVARCHAR(MAX) AS JSON
)
CROSS APPLY OPENJSON([Roles]) R

Upvotes: 1

gotqn
gotqn

Reputation: 43676

Try this:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[{"ID":1,"Roles":[1,2]},{"ID":2, "Roles":[1,2,3]},{"ID":3,"Roles":[1,2,3,4]}]'


SELECT pvt.[ID]
      ,roles.value
FROM
(
    SELECT js.[key] as [key_id]
          ,roles.[key]
          ,roles.value
    FROM OPENJSON(@json) js
    CROSS APPLY OPENJSON(js.value) roles 
) DS
PIVOT
(
    MAX([value]) FOR [key] IN ([ID], [Roles])
) PVT
CROSS APPLY OPENJSON(pvt.roles) roles

enter image description here

or just this:

SELECT JSON_VALUE(js.value, '$.ID')
      ,ds.[value]
FROM OPENJSON(@json) js
CROSS APPLY OPENJSON(JSON_QUERY(js.value, '$.Roles')) ds;

Upvotes: 0

Related Questions