· May 26, 2016 2m read

Using SQL to Create JSON From an Existing Persistent Object

With the release of Cache 2016.1, JSON support was re-architected and made part of the core object model with the creation of %Object and %Array classes, which allow you to create dynamic JSON enabled objects and arrays.

On a recent demonstration I was working on, I had the need to create a REST web service that returned a JSON representation of a persistent object.  After searching for methods that would allow me to accomplish this, ultimately I found none, until now.

With the release of 2016.2 (currently in beta) we introduce the Document Data Model along with SQL enhancements that allow you to query the documents and intermix the information within the document model with data contained within existing relational tables.  Those same SQL enhancements can be used to create a JSON representation of a persistent object.

You can use SQL to query the Sample.Person table and return a JSON representation for each record.  Take the following example Cache ObjectScript:

      SELECT JSON_OBJECT('Name':NAME,'DOB':DOB,'Age':AGE) into :myJSONString FROM SAMPLE.PERSON)                

  &sql(open PERSONS)

  &sql(fetch PERSONS)

  while (SQLCODE=0)
     write !,myJSONString
     &sql(fetch PERSONS)


Looking at the code above, I declare a cursor called PERSONS allowing the script to traverse each record returned from the Sample.Person table.  The query itself uses a new function that allows it to build a JSON object from the columns of the table, selecting, NAME, DOB, and AGE.  For each record it returns a JSON string into the local variable named myJSONString.

It then writes out a JSON object for each record of Sample.Person.  Each JSON object will look something like this:


 { “Name” : “Garcia,Julie F.” , “DOB” : 30778 , “Age” : 91 }

Once I have my data in JSON format, I can do different things with it:

If I were creating a REST web service that returned a persistent object in JSON form I could simply write out myJSONString as the response to the REST service call.

If I wanted to convert this to a dynamic %Object, I could use the $fromJSON system method to convert this to a dynamic object allowing me to work with it using regular object properties, such as:

 Set myObject=##class(%Object).$fromJSON(myJSONString)

Once I’ve converted the JSON string to a dynamic object I am then able to access the methods using standard object syntax, such as:

 Write myObject.Name

Yes, it would still be nice to be able to have a system method that would allow me to just convert an instance of a persistent object into JSON, but until then, this serves as an option to accomplish the goal.

Discussion (20)6
Log in or sign up to continue

Hi Kenneth, 

There is $toJSON method which you can use to convert any persistent object into JSON. Actually any %Registered object has it too!

SAMPLES>set Person=##class(Sample.Person).%OpenId(1)

SAMPLES>set personJson=Person.$toJSON()

SAMPLES>write personJson
{"$CLASSNAME":"Sample.Person","$REFERENCE":"1","Age":65,"DOB":39985,"FavoriteColors":["Yellow"],"Home":{"City":"Fargo","State":"NC","Street":"7424 Main Avenue","Zip":82189},"Name":"Novello,Olga I.","Office":{"City":"Pueblo","State":"MT","Street":"430 Franklin Place","Zip":29528},"SSN":"315-46-7788"}
Cache for UNIX (Apple Mac OS X for x86-64) 2016.2 (Build 657U) Sun May 15 2016 20:35:24 EDT

As you might recognize the fact that JSON support is still work-in-progress, and any next version is getting better than prior. In this case 2016.3 will be better than 2016.2 because there iis added support for $toJSON in resultsets (though I ran this in "latest" so results might look different that in 2016.3FT).

DEVLATEST:12:56:44:SAMPLES>write $system.SQL.Execute("select top 10 Age, DOB from Sample.Person").$compose("%Array").$toJSON()
DEVLATEST:13:30:28:SAMPLES>write $system.SQL.Execute("select top 10 Age, DOB from Sample.Person").$toJSON()

Indeed. Starting with Caché 2016.2 you can call $toJSON() on registered and persistent objects and we will convert it with a standard projection logic into a dynamic entity (%Object or %Array) and serialize it as JSON with a $toJSON() call.

If you want to modify your object before you output it to JSON you can first call $compose on your registered object to convert it to a dynamic object, modify it to your needs and then call $toJSON() on your modified dynamic object.

Later versions will introduce more sophisticated means to influence the standard behavior of $compose. 

The addition of the JSON_OBJECT and JSON_ARRAY SQL functions allows you to easily create JSON from a SQL query as well, as Kenneth pointed out. 

Thank you, Eduard.

My need is relatively unsophisticated, at least in my mind :)

I'm modeling  a JSON request payload for a web service as a Persistent class  ("Patient") that will be used as the message body for an Ensemble Request (of type Ens.Request). The Patient object will be populated from HL7v2 or possibly other sources via a DTL, and the target HTTP operation will serialize the message body to JSON for submission to an external web service.

Your suggestions have given me 98% of what I need ... but I'd like to represent unpopulated values as null rather than "" and/or simply omit keys with null values from the resulting JSON. There are format options for some of the %ZEN.Auxiliary.altJSONProvider methods, but none seem to provide that sort of control (and I swear I saw one or both of my desires addressed in one of the DC threads on JSON serialization but can't find it again).

substituting null for "" is a relatively simple $REPLACE operation; scraping out un-valued keys is perhaps a bit more complicated.

Thanks for clarifying the current status of the new JSON methods. I'm hoping any current issues get resolved soon.

Hmm. If " is is escaped as \", and the value consists of a single " (or ends with ") then it appears as \"" in the JSON output. A simple $REPLACE becomes a slightly less simple $REPLACE(json, ":""""", ":null""") , with fingers crossed that the output from %ToJSON isn't prettified in the future...

Certainly not the end of the world, but I'm a bit concerned that it's the start of a trip down the rabbit hole wink