parsing json values in sql server

Question

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.

Your JSON isn't syntactically valid, and you have two entries for ID 1 -- the latter is easily fixed with a DISTINCT, but is it actually right? — Jeroen Mostert
– Jeroen Mostert, Commented Oct 22, 2018 at 10:53
sorry.ID will not be duplicate.it will be 3 for third one.i changed in the question now.Please check now. — Ram
– Ram, Commented Oct 22, 2018 at 10:55

Jeroen Mostert · Accepted Answer · 2018-10-22 11:07:31Z

1

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

edited Oct 22, 2018 at 11:07

answered Oct 22, 2018 at 11:06

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

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

Comments

gotqn · Accepted Answer · 2018-10-22 11:01:56Z

0

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

or just this:

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

answered Oct 22, 2018 at 11:01

gotqn

44k47 gold badges167 silver badges256 bronze badges

1 Comment

Ram Over a year ago

Thank you very much

Collectives™ on Stack Overflow

parsing json values in sql server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related