Using SQL to Create JSON From an Existing Persistent Object

Primary tabs

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.



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()

Wow!   Not sure how I missed that when I went through the docs looking for it.

Indeed exactly what I was lookimg for.


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. 

So did this ever happen? I'm on 2017.2 and the only output I get is <SYNTAX>. I'll post some code if needed, but it's a straightforward persistent object that I'm using as the payload for an Ensemble Ens.Request object.

You can convert object to dynamic object and output it to JSON:

zn "samples"
set per = ##class(Sample.Person).%OpenId(1)
set obj = ##class(%ZEN.Auxiliary.altJSONProvider).%ObjectToAET(per)
write obj.%ToJSON()

Also check %ObjectToJSON in %ZEN.Auxiliary.altJSONProvider class.

Thanks, Eduard.

So the functionality that a number of posters demonstrated here, where %ToJSON() (or $toJSON()) seemed to have been rolled into Persistent and Registered objects at some point was subsequently removed? This thread is very confusing ...

Additional JSON functionality, such as JSON (de)serialization for arbitrary classes was at one point available but currently under review. It may appear in one of future versions. I've posted a workaround.

You can also check RESTForms - REST API  for your persistent classes, it does support JSON (de)serialization. Another article about RESTForms.

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.

so what you are looking for is something similar to XML IGNORENULL = 1
short time ago I ended up with export to XML and then convert XML to JSON.
Not so impressive but better than chasing unwanted  "".   


Replacing "" with null seems like a better solution to me as " symbol is escaped to \", so "" should not be encountered in JSON, except for empty strings.

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

Have you tried d format flag

d - output Caché numeric properties that have value "" as null

Yes I did, just to see what would happen ... none of my properties are of numeric type, sadly

That must have been where I missed it.   I think I was looking at docs for 2016.1 and didn't see it there 

Definately . $toJSON is the easiest method to get a persistent object to JSON, also more efficient 

SQL method works great too if you don't necessarily want the whole object.

such a juicy thread!  :-D

Thank you all for sharing all the details.

This is excellent information.  I've been spooling data out of SQL cursors manually so far (in 2016.1), and this is definitely making me consider another version upgrade to get access to these features.