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

Answer 1

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

Answer 2

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, '\', ''), '"[', '['), ']"', ']')