New post
Discussion
· Apr 24

What is the best way to output JSON data in a REST API GET request?

#Development Environment #JSON #REST API #InterSystems IRIS

Hi devs!

What is the best way to return JSON in IRIS for a GET request in a REST app?

This is how I do it now:

ClassMethod GetAllPersons() As %Stream.Object
{
    d ..%SetContentType("application/json")
    Set rset = ##class(dc.Sample.Person).ExtentFunc()

    set stream=##class(%Stream.TmpCharacter).%New()
    d stream.Write("[")
    if rset.%Next() {
        Set person = ##class(dc.Sample.Person).%OpenId(rset.ID)
        Do person.%JSONExportToStream(.stream)
    }
    While rset.%Next() {
        d stream.Write(",")
        Set person = ##class(dc.Sample.Person).%OpenId(rset.ID)
        Do person.%JSONExportToStream(.stream)
    }
    d stream.Write("]")
    return stream
}

The query is used vs the following persistent class:

Class dc.Sample.Person Extends (%Persistent, %JSON.Adaptor, %Populate)

{



Property Name As %VarString;



Property Title As %String;



Property Company As %String;



Property Phone As %VarString;



Property DOB As %Date(MAXVAL = "$piece($horolog, "","", 1)");



Property PersonId As %Integer [ Calculated, SqlComputeCode = { set {*}={%%ID}}, SqlComputed ];



/// Index for property DOB

Index DOBIndex On DOB;



ClassMethod AddTestData(amount As %Integer = 10)

{

d ..Populate(amount)

}

}

What I don't like:

1. "Unnecessary" "manual" writes of "[]":

d stream.Write("[")

2. Code block repeat for the first segment to excuse "," :


    if rset.%Next() {
        Set person = ##class(dc.Sample.Person).%OpenId(rset.ID)
        Do person.%JSONExportToStream(.stream)
    }
    While rset.%Next() {
        d stream.Write(",")
        Set person = ##class(dc.Sample.Person).%OpenId(rset.ID)
        Do person.%JSONExportToStream(.stream)
    }

3. I should introduce an "artificial" PersonId to make the response include record ID's (thanks to @Robert Cemper):

Property PersonId As %Integer [ Calculated, SqlComputeCode = { set {*}={%%ID}}, SqlComputed ];

Your ideas?

Discussion (7)2
Log in or sign up to continue
Ashok Kumar T · Apr 24

Hello @Evgeny Shvarov 
I directly use %DymaicObject and %DynamicArray and its methods depends on the response type (array or object) to set the values into the JSON response and I use %WriteResponse to write that response to the API call

 Class sample.Impl Extends  %REST.Impl {
 
 ClassMethod GetAllPersons() As %Stream.Object
{
    d ..%SetContentType("application/json")
    set res =  {"name":"test" 
    do ..%SetStatusCode(200)
    do ..%WriteResponse(res)
    q 1
    
}

Mixed of stream data types and other datas then creates a stream and convert into DynamicObject if required and write that response.

1 0
Ashok Kumar T · Apr 29

Hello @Evgeny Shvarov 

I intended to implement the same logic written by Mr @David Hockenbroch  - specifically, fetching the `rowID` using a query, then opening the ID and invoking JSON adaptor methods to create a JSON object and write it to a stream. However, instead of following that approach, I tried same in SQL by constructed the query using SQL’s JSON_OBJECT and JSON_ARRAY functions to consolidate rows directly at the database level.

Unlike the JSON adaptor, which conveniently exports all columns and fields in a single method, this approach required me to manually specify each column and field in the query. Additionally, I had to use implicit joins to handle object properties, and I couldn’t export entire values of stream properties as well. If the JSON_OBJECT function offered a more direct and extensive way to gather all necessary data, the process would be much more straightforward. 

So, I’ve submitted this as an idea in the idea portal to add this JSON_OBJECT() include all the fields dynamically. It's simple and eliminates the need for object instances.

Sample SQL

SELECT JSON_OBJECT(
        'Name' :Name,
        'Email' :JSON_OBJECT('EmailType' :Email->EmailType->Type,'EmailId':Email->Email),
        'Phone':JSON_ARRAY($LISTTOSTRING(Phone)),
        'Address': JSON_OBJECT(
            'Door':Address->Door,
            'State':JSON_OBJECT(
                'stateId':Address->state->stateid,
                'state':Address->state->state
            ),
            'City':JSON_OBJECT(
                'cityId':Address->city->cityid,
                'city':Address->city->city
            ),
            'Country':JSON_OBJECT(
                'countryId':Address->Country->Countryid,
                'country':Address->Country->Country
            )
        )
    )
FROM Sample.Person
WHERE ID=1

Thank you!

0 0
David Hockenbroch · Apr 25

I would use Dynamic Arrays and Dynamic Objects to make this simpler. Those two are your best friends when working with JSON.

ClassMethod GetAllPersons() As %Stream.Object
{
    d ..%SetContentType("application/json")
    Set rset = ##class(dc.Sample.Person).ExtentFunc()

    set stream=##class(%Stream.TmpCharacter).%New()
    set dynArray = [].%New()
    While rset.%Next() {
        do ##class(dc.Sample.Person).%OpenId(rset.ID).%JSONExportToString(.myPerson)
        Set dynObj = {}.%FromJSON(myPerson)
        do dynArray.%Push(dynObj)
    }
    d stream.Write(dynArray.%ToJSON())
    return stream
}
2 0
Enrico Parisi · Apr 26

To address similar cases some time ago I developed a little utility to export to a %DynamicArray the output from an SQL query.
In my case I had to export existing classes that did not extend %JSON.Adaptor (I'm not even sure %JSON.Adaptor existed at that time).
 

Class Community.SQL2JSON
{

/// Execute am SQL query and returns a %DynamicArray containing the rows (as %DynamocObject) returned by the query
/// RetDynArray is the returned %DynamicArray containing the results
/// Parameters used by the query must be included in the query using placeholders (?) e passed By Reference in the local variable array ParamArray
/// where the root node contains the number of parameters, ie:
/// ParamArray = 1
/// ParamArray(1) = 123
ClassMethod QueryToJSON(ByRef SQLQuery As %String, Output RetDynArray As %DynamicArray, ByRef ParamArray As %String) As %Status
{
	Set sc=$$$OK
	Try {
		Set stSql=##class(%SQL.Statement).%New()
		Set stSql.%ObjectSelectMode=0
		Set rsSql=##class(%SQL.Statement).%ExecDirect(.stSql, .SQLQuery,ParamArray...)
		If rsSql.%SQLCODE < 0 {
			Set sc = $$$ERROR($$$GeneralError,"%SQLCODE="_rsSql.%SQLCODE_", %Message="_rsSql.%Message)
			Quit
		}
		If '$IsObject($g(RetDynArray)) Set RetDynArray = []
		While rsSql.%Next(.sc) {
			If $$$ISERR(sc) Quit
			Set RowDynObj={}

			Set sc=..RowToDynObj(stSql,rsSql,.RowDynObj)
			If $$$ISERR(sc) Quit
			Do RetDynArray.%Push(RowDynObj)
		}
	} Catch CatchError {
		#dim CatchError as %Exception.SystemException
		Set sc=CatchError.AsStatus()
	}
	Quit sc
}

/// Convert a recordset in a %DynamicObject with property name equal to the column name
/// If RowDynObj is passed, then adds the properties are added to it, otherwise creates and returns a new dynamic object
ClassMethod RowToDynObj(StSql As %SQL.Statement, RsSql As %SQL.StatementResult, ByRef RowDynObj As %DynamicObject) As %Status
{
	Set sc=$$$OK
	Try {
		If '$IsObject($g(RowDynObj)) Set RowDynObj= {}
		
		For col=1:1:StSql.%Metadata.columnCount {
			Set ColumnName=StSql.%Metadata.columns.GetAt(col).colName
			Set ColumnValue=$Property(RsSql,ColumnName)
			Do RowDynObj.%Set(ColumnName,ColumnValue)
		}
	} Catch CatchError {
		#dim CatchError as %Exception.SystemException
		Set sc=CatchError.AsStatus()
	}
	Quit sc
}

}

Using it is very simple:

EPTEST>Set SQLQuery="select Name, DOB as ""Birth Date"", Home_City as City from Sample.Person where Home_City=?"
 
EPTEST>Set ParamArray=1
 
EPTEST>Set ParamArray(1)="Newton"
 
EPTEST>Set sc=##class(Community.SQL2JSON).QueryToJSON(SQLQuery,.RetDynArray,.ParamArray)
 
EPTEST>Do RetDynArray.%ToJSON()
[{"Name":"Uhles,Susan D.","Birth Date":31836,"City":"Newton"},{"Name":"Ubertini,Debby N.","Birth Date":42513,"City":"Newton"},{"Name":"Harrison,Rob E.","Birth Date":62265,"City":"Newton"},{"Name":"Adams,Robert E.","Birth Date":62769,"City":"Newton"}]
EPTEST>
2 0