Hey group members,
Recently I came across a new challenge.
I needed to analyze data which is stored as a JSON in a single DB column.
What the heck?!
How can one parse the required data from the huge JSON?!
In my research for a solution to this challenge I found PROC JSON which was quite cute for handling a single JSON (like in HTTP responses) but in my case I need to extract hundreds of fields from a table that contains millions of JSON’s.
Further in my research, I’ve found that almost any language supplies functions to parse JSON data.
To my great astonishment and disappointment SAS does not!
Is this real? SAS? The language that has a function for everything?
Well.. that seems to be the case.
so, since I couldn’t wait for the guys in north Carolina I’ve decided to write one down myself.
This is my first draft
I’ll need to add error-handling, optimization and all but its working.
Please feel free to improve & share
Assaf attas.
%let j_json='{
"quiz":{
"sport":{
"q1":{
"question":"Which one is correct team name in NBA?",
"options":[
"New York Bulls",
"Los Angeles Kings",
"Golden State Warriros",
"Huston Rocket"
],
"answer":"Huston Rocket"
}
},
"maths":{
"q1":{
"question":"5 + 7 = ?",
"options":[
"10",
"11",
"12",
"13"
],
"answer":"12"
},
"q2":{
"question":"12 - 8 = ?",
"options":[
"1",
"2",
"3",
"4"
],
"answer":"4"
}
}
}
}
'
;
options cmplib=work.funcs;
proc fcmp outlib=work.funcs.json;
function json_value(jf_jstr $,jf_value $) $ 256;
/* Function Input */
/*-------------------------------------------------------*/
length j_jstr $ 32767 j_value j_return $ 256;
j_jstr =compbl(jf_jstr);
j_value =compress(jf_value);
/* Declarations & Calc */
/*-------------------------------------------------------*/
length thisObj $ 32 j_object $ 32767;
levels =countw(j_value,'.');
j_object =j_jstr;
/* LOOP Object levels */
/*-------------------------------------------------------*/
do _j=2 to levels;
thisObj =cats('"',scan(j_value,_j,'.'),'"');
/* For Objects */
/*-------------------------------------------------------*/
if _j < levels then do;
/* Start parser */
/*---------------------------------------*/
spos=index(j_object,trim(thisObj));
spos=find(j_object,'{',spos+1 );
epos=spos;
x=1;
/* LOOP parser */
/*---------------------------------------*/
do until (x=0 or max_iter=100);
epos=findc(j_object, '{}',epos+1);
v=substr(j_object,epos,1);
if v='{' then x=x+1;
else if v='}' then x=x-1;
max_iter=sum(y,1); /* Just in case... :) */
end;
/* RETURN parser */
/*---------------------------------------*/
j_object=substr(j_object,spos,(epos-spos+1));
/* CLEAR parser */
/*---------------------------------------*/
spos=0;
epos=0;
x=0;
end;
/* For Key:Value Pair */
/*-------------------------------------------------------*/
else do;
pattern_rgx='/' || '(' || trim(thisObj) || ')(\s*:\s*)((\[([^\]]+)\])|(\"([^\"]+)\")|(\w*))' || '/i';
pattern_ID =prxparse(pattern_rgx);
call prxsubstr(pattern_ID, j_object, j_pos, j_len);
j_return=scan(substr(j_object, j_pos, j_len), 2, ':');
end;
end;
return(j_return);
endfunc;
run;
data sample;
length j_jstr $ 32767;
j_jstr =compbl(&j_json);
q1_question= json_value(j_jstr,'$.quiz.maths.q1.question');
run;
