Parsing JSON objects - Can Dynamic Objects be saved in persistent classes?

Question

Question

Oliver Wilms · Jun 6, 2019

Hello,

I want to parse large JSON object and store data into persistent tables. Is there any other way besides pre-defining persistent classes including typing each property name? Maybe parse it and store properties into a global so that I know what properties need to be defined for each class...

Thanks in advance for your thoughts

Discussion (6)3

Log in or sign up to continue

Stefan Wittmann · Jun 7, 2019

You have various options, depending on what you want to achieve:

Added a property of type %DynamicObject or %DynamicArray to a persistent class
Store the JSON content in the Document Data Model
Parse a JSON structure as Eduard indicated and generate the corresponding persistent class(es).
I would not advise to use %Document.Object. Use the Document Model instead.

With option 1) and 2) you don't need to define a schema at all, but can easily persist and manage your JSON content as objects. No SQL access to individual properties of the JSON content for option 1). With the document data model you can add support for SQL queries for individual JSON paths.

Option 3) is work, but doable. Figuring out the correct datatypes will be the greatest challenge. If you have JSON schema instead, your life is much simpler.

0 0

score 0 · Answer 1 · 2019-06-07T03:55:11-04:00

Check this article on iterating dynamic object.

Instead of just displaying the fields you can use %Dictionary package to generate corresponding class.

After that use new %JSON.Adaptor class (or old %ZEN.Auxiliary.jsonProvider class) to parse JSON into objects.

score 0 · Answer 2 · 2024-02-07T09:11:45-05:00

I am doing option #1, but I'm having the issue now of my content coming out with backslashes in front of all the double-quotes, rendering it unable to be parsed as a true JSON object.

"Vitals": "{\"TemperatureFahrenheit\":\"97.3 F\",\"TemperatureCelsius\":\"36.3 C\",\"TemperatureTimetaken\":\"1/18/24@08:19:25\",\"Pulse\":\"73\" }"

Anyone know why this is happening and how I can fix it? When I query the table in System Management Portal SQL tool, it looks fine, like a normal JSON object, no backslashes, but when I pull it out, the backslashes appear and ruin it.

score 0 · Answer 3 · 2024-02-07T09:51:27-05:00

Can you please explain in detail what you mean with "I pull it out".

From where?
Using objects?
Using SQL?
If SQL, from what language you run the query? What mode?

score 0 · Answer 4 · 2024-02-07T11:23:13-05:00

I use a tool from someone in the community called SQLToObject (because I don't like fussing with cursors and rows) but the result I get is the same whether I query it using ObjectScript and a SQL Command object or doing it this way.
I don't know what you mean by "what mode?" I use ObjectScript in an IRIS Class and run the query and turn the resultant dynamic array of dynamic objects into a JSON object and send it. Or print it. Either way, the result is that one field that I saved in the persistent class as a %DynamicObject ends up (in my object at print or send time) with backslashes in front of all the double quotes and hence cannot be parsed by a consuming client as JSON because JSON cannot have backslashes in it escaping the double quotes like that.
set rootObj = {}
set patientList = []
    set query = "SELECT * FROM REST_FES_Sepsis.SepsisInpatientList"
    try
    {
        set patientList = ##class(REST.FES.Utils.SQLtoObject).Build(query,namespace)
    }
    catch (ex)
    {
        set sc=ex.AsStatus()
        do ex.Log()
        throw ex
    }     set rootObj.InpatientList = patientList
quit rootObj.%ToJSON()

I did discover that when I use the command line, $SYSTEM.SQL.Shell(), and run the SELECT statement there, that the backslashes don't appear. So it's something with the way the data is getting put into into the dynamic object when I run my query through ObjectScript.

score 1 · Answer 5 · 2024-02-07T15:46:38-05:00

I figured out my problem. You have to use the %FromJSON() method on the database value that is a %DynamicObject type. I had tried this before and it didn't work, but I was using %FromJSON() incorrectly. I didn't realize you have to use it like this: set myVal = {}.%FromJSON(DBvalue). I tried to use it like this - set myVal = DBvalue.%FromJSON() and it errored out of course the DB value type doesn't have the %JSON.Adaptor library as part of it. But I just thought when I saw the error that that was the end of that possibility, and that I wouldn't be able to use %FromJSON() on it. Turns out I was just doing it wrong. ;-)