New post
Question
· Jun 9, 2020

Creating JSON objects from ObjectScript objects

#JSON #ObjectScript #InterSystems IRIS #InterSystems IRIS for Health

Say I have an ObjectScript object called Book. It has 2 properties title and author. It extends JSON.%Adaptor, so I can call book.%JSONExport() and get this output:

{ "title": "For Whom the Bell Tolls", "author": "Hemmingway" }

In my web service I want to have a search function that returns an array of Books along with the total number of responses, like this: 

{
    "results": 3,
    "items": [
        { "title": "For Whom the Bell Tolls", "author": "Hemmingway" },
        { "title": "The Invisible Man", "author": "Ellison" }, 
        { "title": "Tender is the Night", "author": "Fitzgerald" }
    ]
}

What's the best way to do this? Can I export an ObjectScript object into a Dynamic Object?

Discussion (13)3
Log in or sign up to continue
Eduard Lebedyuk · Jun 9, 2020

%JSON.Adaptor does not construct an intermediate dynamic object. You can use %ObjectToAET to convert normal object into dynamic object:

set dynObj = ##class(%ZEN.Auxiliary.altJSONProvider).%ObjectToAET(obj)

There are two approaches:

1. Create a "result set" class to hold the results (interestingly, InterSystems provides %XML.DataSet and other tools for this specific use case with XML/SOAP. Docs):

Class Test.JSONRS Extends (%RegisteredObject, %JSON.Adaptor)
{
Property count As %Integer;
Property results As list Of Book;
}

2. Simple approach:

  • Output header {"results": 3, "items": [
  • Call %JSONExport on each book (don't forget the comma at the end)
  • Output footer ]}

Despite being hacky, the second approach is better:

  • If JSON export on each individual object is successful it works every time and if some object fails we won't get valid a JSON anyways
  • It can be easily generalized to use with any type of result
  • It does not hold a large structure in memory as each object is loaded/displayed individually.

That said, I generally recommend against supplying count in query results because of many reasons:

3 0
Muni Ganesh · Jun 9, 2020

Hi Raj,

I just tried answer for your question, I have given the code  below. I hope it helps you.

---------------------------------------------------------------------------------------------------------------------------------

   set array=[]
   set obj = {}
   set obj.title="For Whom the Bell Tolls"
   set obj.author="Hemmingway"
   do array.%Push(obj)    

   set obj = {}
   set obj.title="The Invisible Man"
   set obj.author="Ellison"
   do array.%Push(obj)    

   set obj = {}
   set obj.title="Tender is the Night"
   set obj.author="Fitzgerald"
   do array.%Push(obj)    

 set arraylen=0 
 set iter = array.%GetIterator()
   while iter.%GetNext() {
      set arraylen=$I(arraylen)
   }
   
   set MainObj={}
   set MainObj.results=arraylen
   set MainObj.items=array
   
   w MainObj.%ToJSON()
----------------------------------------------------------------------------------------------------------------

Output:

{"results":3,"items":[{"title":"For Whom the Bell Tolls","author":"Hemmingway"},{"title":"The Invisible Man","author":"Ellison"},{"title":"Tender is the Night","author":"Fitzgerald"}]}

--------------------------------------------------------------------------------------------------------------------

4 1
Max Abrahams · Jun 9, 2020

Just for the sake of variety, (and to advertise the flexiblity of IRIS) i might suggest another way to consider the problem might be to use a SQL query that projects the %Persistent objects into a document format without the need for a JSON adapter

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_jsonobject

or, to consider the %DocDB storage model which would get you this behavior "for free" by storing the %persistent objects natively in JSON format.
 

0 0
Raj Singh · Jun 9, 2020

Thanks all. I was using solution #1 from @Eduard Lebedyuk -- creating a new %RegisteredObject class to hold the response -- because it gives me good control over what is happening while keeping my code easy to understand. The solution using the `%ZEN.Auxiliary.altJSONProvider` class is interesting to keep in mind, but it outputs the class name as a "_class" key , which I don't want, and relying on the old %Zen class library isn't recommended in new solutions.

The answer from @Muni Ganesh works too, but I wanted to do something more elegant than the  "brute force"  approach (but that offers complete control over the process). 

@Max Abrahams you offer an interesting solution too. When I have an opportunity I'll have to try out that SQL-centric approach.

0 0
Vitaliy Serdtsev · Jun 10, 2020

Here is a ready-made example (works even in last Caché):

Class dc.test Extends %Persistent
{

Property title As %VarString;

Property author As %VarString;

/// do ##class(dc.test).test()
ClassMethod test()
{
  &sql(truncate table dc.test)
  
  &sql(insert into dc.test(title,author)
    select 'For Whom the Bell Tolls','Hemmingway' union
    select 'The Invisible Man','Ellison' union
    select 'Tender is the Night','Fitzgerald')
  
  set provider=##class(%ZEN.Auxiliary.altJSONSQLProvider).%New(),
      provider.sql="select title,author from dc.test",
      provider.arrayName="items",
      provider.maxRows = 0,
      provider.%Format = "tw"
  
  do provider.%WriteJSONStreamFromSQL(.stream,,,,,provider)
  
  set json={}.%FromJSON(stream),
      json.results=json.items.%Size()

  write json.%ToJSON()
}

}

Result:

USER>do ##class(dc.test).test()
{"items":[{"title":"For Whom the Bell Tolls","author":"Hemmingway"},{"title":"The Invisible Man","author":"Ellison"},{"title":"Tender is the Night","author":"Fitzgerald"}],"results":3}
2 0
Sylvain Guilbaud · Feb 1

Hi,

I know your question was about ObjectScript, but if Python is allowed in your environment, I suggest to use it to get JSON : 

resultsetJSON.py :

import iris
import json
import getpass
import os
if 'IRISHOSTNAME' in os.environ:
    hostname = os.environ['IRISHOSTNAME']
else:
    hostname = input('hostname [localhost] :') or "localhost"
if 'IRISPORT' in os.environ:
    port = os.environ['IRISPORT']
else:
    port = input('port [1972] :') or "1972"
if 'IRISUSERNAME' in os.environ:
    username = os.environ['IRISUSERNAME']
else:
    username = input('login [_SYSTEM] :') or "_SYSTEM"
if 'IRISPASSWORD' in os.environ:
    password = os.environ['IRISPASSWORD']
else:
    password = getpass.getpass('password [SYS]:') or 'SYS'
if 'IRISNAMESPACE' in os.environ:
    namespace = os.environ['IRISNAMESPACE']
else:
    namespace = input('namespace [IRISAPP] :') or "IRISAPP"
connection_string = hostname + ":" + port + "/" + namespace
connectionIRIS = iris.connect(connection_string, username, password)
cursorIRIS = connectionIRIS.cursor()
print("Connected to",connection_string)

query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
cursorIRIS.execute(query)
 
resultSet = cursorIRIS.fetchall()

list = []
result = {"results":len(resultSet),"items":list}
for x in resultSet:
    row = {"name":x[0],"age":x[1],"dob":x[2]}
    list.append(row)
print(json.dumps(result))
 
connectionIRIS.close()

Result :

python resultsetJSON.py
hostname [localhost] :
port [1972] :51779
login [_SYSTEM] :
password [SYS]:
namespace [IRISAPP] :
Connected to localhost:51779/IRISAPP
{"results": 3, "items": [{"name": "Pantaleo,Mary Q.", "age": 36, "dob": "Monday 07 December 1987"}, {"name": "Underman,Laura I.", "age": 71, "dob": "Friday 16 May 1952"}, {"name": "Huff,Lydia J.", "age": 26, "dob": "Sunday 09 November 1997"}]}
{
"results":3,
"items":[
{
"name":"Pantaleo,Mary Q.",
"age":36,
"dob":"Monday 07 December 1987"
},
{
"name":"Underman,Laura I.",
"age":71,
"dob":"Friday 16 May 1952"
},
{
"name":"Huff,Lydia J.",
"age":26,
"dob":"Sunday 09 November 1997"
}
]
}
0 0
Sylvain Guilbaud · Feb 1

With embedded python you can get your JSON with few lines of code.

resultsetJSONembedded.py :

import os
import getpass
if not 'IRISUSERNAME' in os.environ:
    os.environ['IRISUSERNAME'] = input('set IRISUSERNAME [_SYSTEM] :') or "_SYSTEM"
if not 'IRISPASSWORD' in os.environ:
    os.environ['IRISPASSWORD'] = getpass.getpass('set IRISPASSWORD [SYS]:') or 'SYS'
if not 'IRISNAMESPACE' in os.environ:
    os.environ['IRISNAMESPACE'] = input('set IRISNAMESPACE [IRISAPP] :') or "IRISAPP"
import iris
import json
query = "SELECT top 5 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
rs=iris.sql.exec(query)

list = []
for idx, x in enumerate(rs):
    row = {"name":x[0],"age":x[1],"dob":x[2]}
    list.append(row)
result = {"results":len(list),"items":list}
print(json.dumps(result))

Result :

irispython resultsetJSONembedded.py 
set IRISUSERNAME [_SYSTEM] :
set IRISPASSWORD [SYS]:
set IRISNAMESPACE [IRISAPP] :
{"results": 3, "items": [{"name": "Xander,Dan T.", "age": 35, "dob": "Tuesday 10 January 1989"}, {"name": "Kratzmann,Jane O.", "age": 34, "dob": "Thursday 14 September 1989"}, {"name": "Ingrahm,Bill S.", "age": 82, "dob": "Saturday 01 November 1941"}]}

or if you prefer export environment variables (without exposing the IRISPASSWORD) :

export IRISUSERNAME=_SYSTEM
export IRISNAMESPACE=IRISAPP
irispython resultsetJSONembedded.py 
set IRISPASSWORD [SYS]:
{"results": 3, "items": [{"name": "Xander,Dan T.", "age": 35, "dob": "Tuesday 10 January 1989"}, {"name": "Kratzmann,Jane O.", "age": 34, "dob": "Thursday 14 September 1989"}, {"name": "Ingrahm,Bill S.", "age": 82, "dob": "Saturday 01 November 1941"}]}
{"results": 3, "items": [{"name": "Xander,Dan T.", "age": 35, "dob": "Tuesday 10 January 1989"}, {"name": "Kratzmann,Jane O.", "age": 34, "dob": "Thursday 14 September 1989"}, {"name": "Ingrahm,Bill S.", "age": 82, "dob": "Saturday 01 November 1941"}]}
0 0
Sylvain Guilbaud · Feb 1

Hello, 

with IRIS you can avoid the now deprecated %ZEN.Auxiliary and rather use %DynamicObject / %DynamicArray : 

ObjectScript : 

ClassMethod getJSON() As %String
{
    set query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') as dob FROM sample.person"
    set rs=##class(%SQL.Statement).%ExecDirect(,query)

    set list = []
    while rs.%Next() {
        set row = {"name":(rs.%Get("name")),"age":(rs.%Get("age")),"dob":(rs.%Get("dob"))}
        do list.%Push(row)
    }
    set result = {"results":(list.%Size()),"items":(list)}
    return result.%ToJSON()
}

Result : 

write ##class(resultset).getJSON()
{"results":3,"items":[{"name":"Pantaleo,Mary Q.","age":36,"dob":"Monday 07 December 1987"},{"name":"Underman,Laura I.","age":71,"dob":"Friday 16 May 1952"},{"name":"Huff,Lydia J.","age":26,"dob":"Sunday 09 November 1997"}]}

Embedded Python :  

ClassMethod get() As %String [ Language = python ]
{
import iris
import json
query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
rs=iris.sql.exec(query)

list = []
for idx, x in enumerate(rs):
    row = {"name":x[0],"age":x[1],"dob":x[2]}
    list.append(row)
result = {"results":len(list),"items":list}
return json.dumps(result)
}

Result : 

write ##class(resultset).get()
{"results": 3, "items": [{"name": "Pantaleo,Mary Q.", "age": 36, "dob": "Monday 07 December 1987"}, {"name": "Underman,Laura I.", "age": 71, "dob": "Friday 16 May 1952"}, {"name": "Huff,Lydia J.", "age": 26, "dob": "Sunday 09 November 1997"}]}
0 0