Parsing double stringify JSON data

[ellen09]

Hi,

I retrieve a JSON file which looks like below, and I am trying to parse it. The value of name "C" is quoted as a string, however, it is also an array. I am trying to parse it with both libname json engine and DS2 procedure, but I don't know how to parse inside the value of "C".  Any help appreciated!

{
"result": {
"status": "good"
},
"reviews": [
{
"A": 32,
"B": "name1",
"C": "{\"x1\": {\n \"A1\": {\"y1\": [\n {\n \"fname\": \"p1\",\n \"mname\": \"p2\",\n }\n ]\n}}",
"D": "New"
},
{
"A": 38,
"B": "name2",
"C": "{\"x1\": {\n \"B1\": {\"y1\": [\n {\n \"fname\": \"q1\",\n \"mname\": \"q2\",\n }\n ]\n}}",
"D": "New"
}
]
}

[ChrisNZ]

What's the desired output?

[ellen09]

I am hoping to get a data set like this:

[ChrisNZ]

The simplest if the JSON's structure is quite constant:

FNAME=scan(C, 9,':\"{}');

MNAME=scan(C,12,':\"{}');

If the string varies more, you may have to look for the positions of fname and mname.

If it varies a lot, you may have to resort to regular expressions to isolate the bits you want, or use several statements.

A data step allows you to read the JSON string using an input statement, but DS2 cannot afaik:

https://communities.sas.com/t5/SAS-Data-Management/Parsing-a-string-variable/td-p/157459

[ellen09]

That helps. Thank you