Parsing JSON stored in a table column

[viola]

I'm curious to know if anyone has had luck parsing JSON stored in a column. I'm using proc JSON to successfully parse a JSON file from my company's web application into a collection of tables. However, because some of the settings for our application are stored in our database as JSON, a few of the columns contain JSON after they are parsed into the tables. 

I'm having trouble figuring out how to further parse those values. What doesn't help is that some of them are stored as new-line JSON objects, and thus contain '\n', which SAS doesn't seem to like (just for kicks, I tried parsing one of the objects through proc JSON and I got some kind of an "invalid format" error). I've seen some examples of people using data steps to tell SAS exactly what to find where, except in my case, what's contained in the JSON column is not always the same, or predictable.

Would love any tips based on others' experiences, or other creative solutions! 

[ChrisNZ]

Many similar questions:

https://communities.sas.com/t5/SAS-Programming/Parsing-JSON-stored-under-a-Data-set-Column/td-p/3345...

https://communities.sas.com/t5/General-SAS-Programming/Parsing-json-data/td-p/399736

https://communities.sas.com/t5/SAS-Data-Management/Parsing-a-string-variable/td-p/157459

Have you looked? Anything specific that doesn't apply to your data?

[viola]

Thank you @ChrisNZ, I will look these links over. I've scoured the internet far and wide in search of a situation that applies to me, and so far, no luck...Once I have a read I can get back to you with more specifics.

[Ksharp]

https://blogs.sas.com/content/sasdummy/2018/11/14/jsonl-with-proc-json/

[Tom]

---

@Ksharp wrote:

https://blogs.sas.com/content/sasdummy/2018/11/14/jsonl-with-proc-json/

---

That post shows a method for generating the JSONL format.  The question here is how to deal with JSONL format that is already included.  One method is to do the reverse operation and convert the JSONL into valid JSON syntax.

Here is a simplified way to do what Cris's blog posts does. Generate a JSONL file from a SAS dataset. It use PROC JSON to make a JSON file and then converts it to a JSONL file.

filename json temp;
filename jsonl temp;

proc json out=json nosastags pretty;
  export sashelp.class(obs=5);
run;
data _null_;
  infile json truncover;
  file jsonl;
  input line $32767.;
  if line in ('[',']') then return;
  if line in ('}','},') then put '}';
  else put line @;
run;

So here is a program to convert a JSONL file into a valid JSON file by adding back the enclosing square brackets and commas.

filename json2 temp;
data _null_;
  file json2 ;
  infile jsonl end=eof ;
  if _n_=1 then put '[';
  if eof then put ']';
  input ;
  put _infile_ @;
  if not eof then put ',';
  else put;
run;

libname json2 json ;
proc print data=json2.root;
run;

            ordinal_
     Obs      root       Name      Sex    Age    Height    Weight

       1        1       Alfred      M      14     69.0      112.5
       2        2       Alice       F      13     56.5       84.0
       3        3       Barbara     F      13     65.3       98.0
       4        4       Carol       F      14     62.8      102.5
       5        5       Henry       M      14     63.5      102.5

You could easily adapt that data step to generate the JSON file from the JSONL records in your existing SAS dataset and then use the JSON libname engine to read it back in.

[viola]

Thanks @Tom - you're exactly right - I am trying to parse JSON lines, not create it, though I have read that blog post by Chris. I'm going to see if I can get the code you posted to work for me.

[viola]

I have an update to my problem - the JSON lines are not actually what's causing the issue. The issue is that the JSON is double-escaped because it was stored as text in the original database. That's why SAS won't parse it. 

[{"myText":"Text", "myJson":"{\"day\":23}"}]

It's the piece highlighted in red that is being stored under the column "myJSON" once I upload the file via the JSON libname engine. I need SAS to treat what's in red as JSON and not text. I have tried isolating the column and writing it back to a JSON file, but I keep getting errors that the JSON is not valid because of the colons. 

[ChrisNZ]

Can't you just replace  \"  with  "   as you load the string?

[viola]

@ChrisNZ would that mean loading in the JSON as text first, to edit out the \" and then using the JSON libname engine to import the edited text? 

[ChrisNZ]

Rewriting the file is a solution.

I was thinking of loading the value as-is, with the escape characters, and parsing it yourself if possible.

I can't do any tests as my version of SAS is so old it doesn't feature the JSON libname engine.

[SASJedi · Accepted solution]

This question involved a lot of discussion, with @Ksharp, @ChrisNZ and @Tom all providing answers as the question evolved. In the interest of making this information more readily accessible to future readers, and to add a bit of my own input to simplify the process, I’m going to summarize and embellish those solutions here.

Problem definition:

@viola had a SAS dataset where one column contained JSON text captured from external applications. The JSON was not consistently formatted and was difficult to parse. Some JSON was newline delimited JSON (ndJSON), which most standard JSON parsers can’t successfully parse. Some of the JSON had embedded hex characters that interfered with parsing. And finally, some had “double escaped” values that looked like this: [{"myText":"Text", "myJson":"{\"day\":23}"}]

Proposed process:

1. Write the JSON contained in each JSON variable out to a text file
2. Correct the formatting issues
3. Copy the data into a SAS library using the Base SAS JSON LIBNAME engine.

Create some data to play with:

/* Set up */
/* PATH is an existing file system location */
%let path=C:/temp/JSON;
LIBNAME JSON "&path";

data json.source_data_messy;
  length ID 8 Name $50 JSON $32767;
  /* JSON Windows formatted: CR\LF ('0D0A'x) for new line */
  ID=1;
  Name='Windows formatted JSON - CR\LF new line';
  JSON=cats('{"Actors":[','0D0A'x
           ,'{"name":"Tom Cruise","age":56,"Born At":"Syracuse, NY","Birthdate":"July 3, 1962"},'
           ,'0D0A'x
			  ,'{"name":"Robert Downey Jr.","age":53,"Born At":"New York City, NY","Birthdate":"April 4, 1965"}'
           ,'0D0A'x
			  ,']}'
           ,'0D0A'x
		 );
  output;
  /* JSON *nix formatted: LF ('0A'x) for new line */
  ID=2;
  Name='*nix formatted JSON - LF for new line';
  JSON=cats('{"Actors":[','0D0A'x
           ,'{"name":"Tom Cruise","age":56,"Born At":"Syracuse, NY","Birthdate":"July 3, 1962"},'
           ,'0A'x
			  ,'{"name":"Robert Downey Jr.","age":53,"Born At":"New York City, NY","Birthdate":"April 4, 1965"}'
           ,'0A'x
			  ,']}'
           ,'0A'x
		 );
  output;
  /* Newline delimited JSON - no heirarchy, CR\LF delimited*/
  ID=3;
  Name='Newline Delimited JSON';
  JSON=cats('{"name":"Tom Cruise","age":56,"Born At":"Syracuse, NY","Birthdate":"July 3, 1962"}'
           ,'0A'x
			  ,'{"name":"Robert Downey Jr.","age":53,"Born At":"New York City, NY","Birthdate":"April 4, 1965"}'
           ,'0A'x
		 );
  output;
  /* Windows delimited JSON double-escaped */
  ID=4;
  Name='Windows formatted JSON double-escaped';
  JSON=cats('{"Actors":[','0D0A'x
           ,'{\"name\":\"Tom Cruise\",\"age\":56,\"Born At\":\"Syracuse, NY\",\"Birthdate\":\"July 3, 1962\"},'
           ,'0D0A'x
			  ,'{\"name\":\"Robert Downey Jr.\",\"age\":53,\"Born At\":\"New York City, NY\",\"Birthdate\":\"April 4, 1965\"}'
           ,'0D0A'x
			  ,']}'
           ,'0D0A'x
		 );
  output;
run;
/* A quick look at the source data */
title "Messy Source Data Set with JSON Column";
proc print data=json.source_data_messy;
run;

Result:

Obs	ID	Name	JSON
1	1	Windows formatted JSON - CR\LF new line	{"Actors":[ {"name":"Tom Cruise","age":56,"Born At":"Syracuse, NY","Birthdate":"July 3, 1962"}, {"name":"Robert Downey Jr.","age":53,"Born At":"New York City, NY","Birthdate":"April 4, 1965"} ]}
2	2	*nix formatted JSON - LF for new line	{"Actors":[ {"name":"Tom Cruise","age":56,"Born At":"Syracuse, NY","Birthdate":"July 3, 1962"}, {"name":"Robert Downey Jr.","age":53,"Born At":"New York City, NY","Birthdate":"April 4, 1965"} ]}
3	3	Newline Delimited JSON	{"name":"Tom Cruise","age":56,"Born At":"Syracuse, NY","Birthdate":"July 3, 1962"} {"name":"Robert Downey Jr.","age":53,"Born At":"New York City, NY","Birthdate":"April 4, 1965"}
4	4	Windows formatted JSON double-escaped	{"Actors":[ {\"name\":\"Tom Cruise\",\"age\":56,\"Born At\":\"Syracuse, NY\",\"Birthdate\":\"July 3, 1962\"}, {\"name\":\"Robert Downey Jr.\",\"age\":53,\"Born At\":\"New York City, NY\",\"Birthdate\":\"April 4, 1965\"} ]}

Now, let's get to work.

1. Writing the JSON to text files

We’ll use a DATA _NULL step to read the original SAS data set, put some information into macro variables to help subsequent processing, and then write the JSON out to a series of text files.

/* Actual work begins here*/
/* JSON files default encoding is UTF8. If yours is different, set it here:*/ 
%LET ENCODING=utf8;
/* Read the source data, write the JSON out to text files*/
data _null_;
	length name $256 file_name $1000;
	/* Read the source data */
	set json.source_data_messy end=last;
	/* Create a name for the JSON file */
   file=cats("json_text_",ID,'.json');
   file_name=cats("&path/",file);
	/* Direct output to the JSON file */
	file out filevar=file_name;
	/* Write the JSON variable contents to the file */
	put JSON;
	/* Write name of this JSON file to a macro variable */
	call symputx(cats('JSON',_n_),file);
	/* Write the description of this JSON file to a macro variable */
	call symputx(cats('JSON_desc',_n_),Name);
	/* After writing all he JSON files, remember how many there were */
   if last then call symputx('jsonFiles',_n_);
run;

/* View the macro variables created */
title "List of macro variables created";
proc sql;
select Name, Value
	from dictionary.macros
	where name like 'JSON%'
;
quit;

Result:

Macro Variable Name	Macro Variable Value
JSON1	json_text_1.json
JSON2	json_text_2.json
JSON3	json_text_3.json
JSON4	json_text_4.json
JSONFILES	4
JSON_DESC1	Windows formatted JSON - CR\LF new line
JSON_DESC2	*nix formatted JSON - LF for new line
JSON_DESC3	Newline Delimited JSON
JSON_DESC4	Windows formatted JSON double-escaped

2. Correct the formatting issues

For ndJSON files, we need to add {"records":[ to the beginning of the file,  add a comma between the elements, and close the file with '{}]}';. For all files, we need to remove the \ from any \” or \’ combination, and finally, remove any stray hex codes that could interfere with parsing. JSON files are UTF-8 encoded by default, so we’ll use the KCOMPRESS function to get rid of hex characters. The TRANWRD function will help us with the rest of the word. Because we have to do this for every JSON file, a macro will make it easier to execute. In the macro, we’ll loop over all the files listed in the JSON1-JSON4 macro variables:

%macro fixJSON;
/* This macro program generates SAS data from the JSON files */
/* Loop processes every JSON file created in the DATA _NULL_ step */
%do I=1 %to &jsonFiles;
	/* Assign a fileref to this JSON file */
	filename i "&path/&&JSON&i" ENCODING="&ENCODING";
	/* Assign a fileref to the cleaned-up JSON file */
	filename j "&path/mod_&&JSON&i" ENCODING="&ENCODING";
	data _null_;
		retain ndJSON 0;
		/* Read from original JSON file */
		infile i lrecl=32767 end=lastrec;
		/* Write to modified JSON file */
		file j;
		/* Get a record */
		input;
		if _n_=1 and not(find (_infile_,'[')) then do;
			ndJSON=1;
		/* Add prefix to ndJSON files */
			put '{"records":[';
		end;
		/* Add commas between elements for ndJSON files */
		if ndJSON then _infile_=tranwrd(_infile_,'}','},');
		/* Remove stray HEX characters from the JSON */
		/* Using KCOMPRESS for UTF8 data */
		_infile_=kcompress(_infile_,,'h');
		/* Fix any double-escaped data */
		_infile_=tranwrd(_infile_,'\"','"');
		_infile_=tranwrd(_infile_,"\'","'");
		/* Write the cleaned-up record */
		put _infile_;
		if lastrec and ndJSON then do;
		/* close out ndJSON files */
			put '{}]}';
		end;
	run;
	filename i clear;
	filename j clear;
%end;
%mend;

3. Copy the JSON data to SAS libraries 

Finally, we want to extract the data from each JSON file to a separate SAS library. The DLCREATEDIR system option will create the directory specified by a BASE LIBNAME statement if it does not already exist. I’ll use that to create the folder for the individual SAS libraries so that any tables that have common names in the JSON files won’t overwrite each other when extracted to SAS. We’ll use the JSON LIBNAME engine to read the cleaned-up JSON files and automate that all in another macro:

%macro importJSON;
%do I=1 %to &jsonFiles;
	/* Assign a fileref to the cleaned-up JSON file */
	filename j "&path/mod_&&JSON&i" ENCODING="&ENCODING";
	/* Assign a libref to the cleaned up JSON file using the JSON engine*/
	libname j JSON;
	/* Create a subdirectory to hold the SAS data, and assign a libref */
	options dlcreatedir;
	LIBNAME o "&path/JSON&i";
	options nodlcreatedir;
	/* Copy all tables from the JSON library to the SAS library */
	proc copy in=j out=o;
	run;
	/* Make a report of the SAS datasets created */
	Title1 """&&JSON_desc&i"" Data SAS Tables";
	Title2 "from &&JSON&i";
	proc contents data=o._all_ nods;
	run;
	/*	Clear the temporary filerefs, librefs and titles */
	libname o clear;
	libname j clear;
	filename j clear;
	title;
%end;
%mend;

Now, we execute our macros, and the work is done!

%fixJSON
%importJSON

For ease of use, I've attached the entire SAS program in a text file that you can download to play with.  

May the SAS be with you!

Mark

[Tom]

By "ndJSON" do you mean JSONL (aka JSON Lines)?

https://jsonlines.org/

[SASJedi]

@Tom By ndJSON I mean  newline delimited JSON -  see http://ndjson.org

[Tom]

So that is the same thing as the JSON lines site is describing.

Makes you wonder about how valid a format it is if they can't even agree on what to call it.  Or why the two sites don't just cross link to each other?