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 query
    set 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.StatementResult
    set rs=##class(%SQL.Statement).%ExecDirect(,query) 
    /// instantiate a %DynamicArray
    set 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 %DynamicArray
        do list.%Push(row) 
    }
    // instantiating another %DynamicObject 'result'
    // composed from the contents of the %DynamicArray
    set result = {
            "results":(list.%Size())
            ,"items":(list)
    } 
    // export data from %DynamicObject
    // in JSON string format
    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 : 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 set
for 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 format
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"}]}

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
{

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

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
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 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"}]}