In my situation I have a column in my SQL table, responseJSON. The JSON is represented as a string value such that it can contain multiple observations, with each observation bracketed with curly brackets ({ }). Within each observation is a set of attributes, with each attribute separated with a comma. Each attribute is made up up a label and a value, separated with a comma. Each lable value pair is separaed with a colon ' : '. An example looks like this:
[{"label_1_1":"value_1_1","label_1_2":"value_1_2","label_1_3":"value_1_3","label_1_4":"value_1_4"},{"label_2_1":"value_2_1","label_2_2":"value_2_2","label_2_3":"value_2_3","label_2_4":"value_2_4"}]
I used a do loop and the scan function to create a row for each observation and extract the value of interest to me. Here is some example code:
data want;
set have;
/** loop through each observation within the JSON object **/
do i = 1 to countc(responsejson, '}');
count1 = countc(responsejson, '}');
/** return the i observation within the JSON object **/
scan1 = compress(scan(responsejson, i, '}'),'"[]{');
/** return the 2nd attribute within the i observation **/
second_attr = scan(scan1, 2, ',');
/** return the 3rd attribute within the i observation **/
third_attr = scan(scan1, 3, ',');
/** return the value from second_attr **/
val1 = scan(second_attr, 2, ':');
/** return the value from third_attr **/
val2 = scan(third_attr, 2, ':');
/** create one row for each obs in JSON object **/
output;
end;
run;
For my application I only wanted to return the value from the second and third attribute from each JSON observation. I am not sure if my terminology is right on this. I am getting pretty good results for my purposes, so I thought I would share in case it helps. I am sure there are much more elegant ways to approach this so feel free to let me know if you come up with something better.
... View more