Working with Json Object from SQL Table

[PatricktLeon]

Hello SAS Community,

I was hoping that someone could assist me in working with a json object that is stored as a field in a SQL table.  I can bring in the entire json object with an ODBC connection and the DBMAX_TEXT=32767 option.  I am having a hard time finding documentation on working with a json object that is stored in a SQL table.  I would like to use the json engine to parse the object, but all of the documentation that I'm finding is related to hitting an API directly.  In this case I already have the object in a SQL table; can someone assist me in finding the correct process for this?  I am working in EG 8.1, SAS 9.4M6.

Here is a shortened example of the json object (enclosed in one cell on each row of the table):

{"Id":0,"Token":"TUVWXYZ","LastPage":3,"FirstName":"First,"LastName":"Last","Age":99,"q1":1,"q2":1,"q3":1,"ActionStep":0,"TokenId":0}

Thank you in advance!

[Tom]

There are a number of questions on this topic in this forum.

In general the SAS tools for working with JSON data require that the text of the JSON is in a FILE, not in a variable.  So you could write the data to a file and then parse the file.

You might also what to check if the source database has JSON parsing tools that you could use to just pull the information you want out of the JSON using those commands and just transfer regular variables to SAS.

[PatricktLeon]

Hi Tom,

I really appreciate your response.  I went with your first suggestion of writing the data to a .txt file.  However, I can only get the JSON engine to parse the file if I create an individual .txt file for each record.  This is not ideal, as my data consists of thousands of records and additional records are added daily.  I can write a macro loop to export a .txt file for each record up to and including &SQLOBS, and then write another loop to parse the files with the JSON engine but I don't really want to create 50,000+ .txt files that are 3 KB each, it doesn't sound ideal as far as processing.  I will continue to research!      

[supp]

I don't have  solution but I do  have this exact same scenario. If you find a solution it would be appreciated if you could update this post. I did find this thread where it appears they are reading JSON in a datastep, maybe it will help.

https://communities.sas.com/t5/SAS-Programming/Process-JSON-with-infile-statment/m-p/313400/highligh...

[supp]

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.