Parsing large JSON files embedded in columns

[mtgkooks]

I am using an Oracle pass-thru in SAS to bring in the first 10 rows of a dataset that has twelve columns, ten of which contain identifying data and the remaining two ("REQUEST_PAYLOAD" and "RESPONSE_PAYLOAD"), each containing a separate JSON file.

Due to the lengths of these strings, I have had to break them into 3200 character chunks. (Each JSON file contains 5,700+ key/value pairs, and has a very complex structure.)

Here's how I'm bringing in the data:

libname ora_test ORACLE USER=XXXXXX password=XXXXXXXX
PATH=XXXXXX SCHEMA=XXXXXXXX;

proc sql;
connect using ora_test;
create table work.qdata as 
select * from connection to ora_test

(select qid,qrefid,created_date,updated_date,qtype,lob,srcsys,gwqid,brefid,pnum,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,1) as ReqChunk1,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,3201) as ReqChunk2,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,6401) as ReqChunk3,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,9601) as ReqChunk4,
        dbms_lob.substr(REQUEST_PAYLOAD,3200,12801) as ReqChunk5,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,16001) as ReqChunk6,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,19201) as ReqChunk7,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,22401) as ReqChunk8,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,25601) as ReqChunk9,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,28801) as ReqChunk10,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,32001) as ReqChunk11,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,35201) as ReqChunk12,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,38401) as ReqChunk13,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,41601) as ReqChunk14,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,44801) as ReqChunk15,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,48001) as ReqChunk16,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,51201) as ReqChunk17,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,54401) as ReqChunk18,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,57601) as ReqChunk19,
	    dbms_lob.substr(REQUEST_PAYLOAD,3200,60801) as ReqChunk20,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,1) as RespChunk1,
        dbms_lob.substr(RESPONSE_PAYLOAD,3200,3201) as RespChunk2,
        dbms_lob.substr(RESPONSE_PAYLOAD,3200,6401) as RespChunk3,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,9601) as RespChunk4,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,12801) as RespChunk5,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,16001) as RespChunk6,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,19201) as RespChunk7,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,22401) as RespChunk8,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,25601) as RespChunk9,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,28801) as RespChunk10,
        dbms_lob.substr(RESPONSE_PAYLOAD,3200,32001) as RespChunk11,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,35201) as RespChunk12,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,38401) as RespChunk13,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,41601) as RespChunk14,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,44801) as RespChunk15,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,48001) as RespChunk16,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,51201) as RespChunk17,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,54401) as RespChunk18,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,57601) as RespChunk19,
	    dbms_lob.substr(RESPONSE_PAYLOAD,3200,60801) as RespChunk20
	from XXXXXX.INFO t fetch next 10 rows only);

disconnect from ora_test;

quit;

REQUEST_PAYLOAD breaks into 12 pieces and totals around 38,000 characters. RESPONSE_PAYLOAD breaks into 15 pieces and totals around 47,000 characters. Due to the combined length of these strings, I can't concatenate either JSON into a single field to be parsed using the JSON libname engine.

I'm wondering if anyone can offer some insight into how I might go about doing this.

Note: I would love to post an example of the output but I cannot, as the file contains PII data.

[Tom · Accepted solution]

Don't.  Since Oracle created that monster find out what the Oracle syntax is for parsing out the data from JSON blobs and use pass thru SQL to query the data in Oracle.

If you want to explore what is in one of those files then use a data step to convert it back into a file and then you can use the JSON libname engine to parse it.

filename json temp;
data _null_;
  set qdata (obs=1);
  file json recfm=n ;
  put (ReqChunk1-ReqChunk20)  ($char3200.) ;
run;

libname json json ;

proc contents data=json._all_; 
run;

proc copy inlib=json outlib=work;
run;

If you really have to deal with those JSON blobs on the SAS side then figure out how to replicate that for every one of them.