How to extract data from nested JSON in SQL Server

Question

DECLARE @CONTACTS varchar(max)
SET @CONTACTS = 
N'[
{"contacts":"[{\"idRole\":1,\"cdsid\":\"RWILS351\"},{\"idRole\":3,\"cdsid\":\"EKANOUS\"},{\"idRole\":126,\"cdsid\":\"RLAESCH1\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"RWILS351\"},{\"idRole\":3,\"cdsid\":\"EKANOUS\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"OINES\"},{\"idRole\":1,\"cdsid\":\"YSEGARRA\"},{\"idRole\":3,\"cdsid\":\"OINES\"},{\"idRole\":3,\"cdsid\":\"TISMAIL3\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DBELL30\"},{\"idRole\":3,\"cdsid\":\"DBELL30\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DBELL30\"},{\"idRole\":3,\"cdsid\":\"DBELL30\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2},{\"pmtGroup\":5}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2},{\"pmtGroup\":5}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":1,\"cdsid\":\"DSANGINE\"},{\"idRole\":3,\"cdsid\":\"DSANGINE\",\"idAttribute\":[{\"pmtGroup\":2}]},{\"idRole\":3,\"cdsid\":\"EDESMET\"},{\"idRole\":126,\"cdsid\":\"NSPENC16\"}]"},
{"contacts":"[{\"idRole\":3,\"cdsid\":\"THERMANN\"}]"}]'

SELECT d.idRole, d.cdsid,e.pmtGroup
FROM  OPENJSON (@CONTACTS)
WITH (
    contacts nvarchar(max) AS JSON
) as c 
CROSS APPLY OPENJSON (c.contacts) 
WITH (
    idRole INT '$.idRole',
    cdsid NVARCHAR(50) '$.cdsid',
    idAttribute NVARCHAR(MAX) '$.idAttribute' AS JSON
) as d
CROSS APPLY OPENJSON(d.idAttribute)
WITH (
    pmtGroup NVARCHAR(8) '$.pmtGroup'
) as e;

I need to get the values idRole, cdsid, pmtGroup from the JSON. What am I missing in my query? I did some research and I tried the cross apply but no data is being returned

Although it's JSON it's probably not the JSON that you're expecting. Each "contacts" property is a JSON string, not an array of objects like you're trying to query. i.e.: remove the AS JSON from contacts nvarchar(max) AS JSON. — AlwaysLearning
– AlwaysLearning, Commented Apr 21, 2022 at 5:31

Charlieface · Accepted Answer · 2022-04-21 12:08:47Z

The problem is that the value contained in contacts is not a JSON object, it's a string containing a serialized JSON object.

So you need to remove AS JSON to retrieve it.

Furthermore, if you want to get the objects which have no pmtGroup you need OUTER APPLY

SELECT d.idRole, d.cdsid,e.pmtGroup
FROM  OPENJSON (@CONTACTS)
WITH (
    contacts nvarchar(max)
) as c 
CROSS APPLY OPENJSON (c.contacts) 
WITH (
    idRole INT,
    cdsid NVARCHAR(50),
    idAttribute NVARCHAR(MAX) AS JSON
) as d
OUTER APPLY OPENJSON(d.idAttribute)
WITH (
    pmtGroup NVARCHAR(8)
) as e;

Alternatively you can just use a path $.idAttribute[0].pmtGroup

SELECT d.idRole, d.cdsid,d.pmtGroup
FROM  OPENJSON (@CONTACTS)
WITH (
    contacts nvarchar(max)
) as c 
CROSS APPLY OPENJSON (c.contacts) 
WITH (
    idRole INT,
    cdsid NVARCHAR(50),
    pmtGroup NVARCHAR(8) '$.idAttribute[0].pmtGroup'
) as d;

db<>fiddle

Ed Bangga · Accepted Answer · 2022-04-21 06:40:00Z

1

first, is your are using openjson so you should supply correctly formatted json data. Add this line to format your data before your operation.

also, no need to terminate " since this is a recognized sql character.

SET @CONTACTS= REPLACE(REPLACE(REPLACE(@CONTACTS, '\', ''), '"[', '['), ']"', ']')

edited Apr 21, 2022 at 6:40

answered Apr 21, 2022 at 6:31

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

2 Comments

Zhorov Over a year ago

The JSON from the question is valid. As @AlwaysLearning explained, the solution is to remove ... AS JSON from the first OPENJSON() call.

Ed Bangga Over a year ago

@Zhorov, yes, its a valid json data, but "contacts":"this is considered to be string value not json data" value for "contacts" is treated as string not json data.

Collectives™ on Stack Overflow

How to extract data from nested JSON in SQL Server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related