Select Attribute from PARSE function pulling data from nested and escaped JSON

Question

I have a deep nested JSON that contains both escaped and non-escaped data. The raw response is escaped with " characters I am trying to get the values for "requestId" but its returning NULLs no matter what I do. Here is a snippit of the JSON data. I can query and parse the data at the "RawResponse" node, but when I go into the RawResponse and try to pull requestID I get NULL.

Is there perhaps a need to do some kind of sub query against the result of the parsed function for RawResponse? Just getting started with JSON queries so any help would be truly appreciated.

"RawResponse": "{\"success\":true,\"requestId\":\"rrt-04a6b53f6c9c2db7d-c-wo-17410-5591259-1\",\"comments\":\"\",\"results\":

SELECT
CREDIT_ATTRIBUTES_RAW:BusinessData.AccountID::string as "AccountID",
CREDIT_ATTRIBUTES_RAW:TransactionData.ApplicationId::string as "ApplicationID",
PARSE_JSON(CREDIT_ATTRIBUTES_RAW:Report.CreditPremier.RawResponse.requestId::string) as "Premier"
FROM TABLEA

user_14442664 · Answer

I trimmed down your json data to test out and below is sql which helps you to drive and pull needed columns:

select try_parse_json('{\"success\":true,\"requestId\":\"rrt-04a6b53f6c9c2db7d-c-wo-17410-5591259-1\"}') as t1, t1:requestId;

Select Attribute from PARSE function pulling data from nested and escaped JSON

1 Answers