Get a result in JSON from an SQL query

Hello Community,

SQL language remains the most practical way to retrieve information stored in a database.

The JSON format is very often used in data exchange.

It is therefore common to seek to obtain data in JSON format from SQL queries.

Below you will find simple examples that can help you meet this need using ObjectScript and Python code.

ObjectScript : using Dynamic SQL with %SQL.Statement +  JSON structures with %DynamicObject and %DynamicArray

ClassMethod getJSON() As%String
{
    // declaration of an SQL queryset query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') as dob FROM sample.person"// instantiation + execution of a dynamic SQL query// with the result in a result set of type %SQL.StatementResultset rs=##class(%SQL.Statement).%ExecDirect(,query) 
    /// instantiate a %DynamicArrayset list = [] 
    // loop through the result set while rs.%Next() {  
        // instantiating a %DynamicObject 'row'set row = { 
                "name":(rs.%Get("name"))
                ,"age":(rs.%Get("age"))
                ,"dob":(rs.%Get("dob"))
            } 
        // adding an element to the %DynamicArraydo list.%Push(row) 
    }
    // instantiating another %DynamicObject 'result'// composed from the contents of the %DynamicArrayset result = {
            "results":(list.%Size())
            ,"items":(list)
    } 
    // export data from %DynamicObject// in JSON string formatreturn 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 : using  iris and json modules

ClassMethod get() As %String [ Language = python ]
{
import iris
import json
# declaration of an SQL query
query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"# instantiation + execution of a dynamic SQL query# with result in a result set of type %SYS.Python.SQLResultSet
rs=iris.sql.exec(query)
# instantiating of a list type structure
list = []
# loop through the result setfor idx, x in enumerate(rs):
    # instantiation of a 'row' dictionary type structure
    row = {
        "name":x[0]
        ,"age":x[1]
        ,"dob":x[2]
    }
    # adding an item to the list
    list.append(row)
# instantiation of another 'result' dictionary type structure# composed from the contents of the list type structure
result = {
    "results":len(list)
    ,"items":list
}
# export dictionary type data# in JSON string formatreturn 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"}]}

As you can see, the code is very similar between these 2 methods; ObjectScript and Python offer relatively similar ways to manipulate JSON structures.

Class User.resultset
{

/// DescriptionClassMethod 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)
}

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

}

You can also meet this same need using the following Python modules:

resultsetJSON.py : using  Python DB-API and json modules

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
print("Connecting to",connection_string)
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] :
Connecting to localhost:51779/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"}]}

resultsetJSONembedded.py : using  iris and json modules

import os
import getpass
ifnot'IRISUSERNAME'in os.environ:
    os.environ['IRISUSERNAME'] = input('set IRISUSERNAME [_SYSTEM] :') or"_SYSTEM"ifnot'IRISPASSWORD'in os.environ:
    os.environ['IRISPASSWORD'] = getpass.getpass('set IRISPASSWORD [SYS]:') or'SYS'ifnot'IRISNAMESPACE'in os.environ:
    os.environ['IRISNAMESPACE'] = input('set IRISNAMESPACE [IRISAPP] :') or"IRISAPP"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}
print(json.dumps(result))

Result :

export IRISUSERNAME=_SYSTEM
export IRISNAMESPACE=IRISAPP
irispython resultsetJSONembedded.py 
set IRISPASSWORD [SYS]:
{"results": 3, "items": [{"name": "Ihringer,Nellie O.", "age": 32, "dob": "Friday 17 January 1992"}, {"name": "Koenig,Sally J.", "age": 34, "dob": "Monday 25 December 1989"}, {"name": "Uberoth,Belinda I.", "age": 47, "dob": "Thursday 11 March 1976"}]}