Creating JSON From SQL

1. Define Persistent Class
Call utility class to fetch json via query.

Class Test.JSONFromSQL Extends (%Persistent, %Populate)
{
Property FirstName As %String(POPSPEC = "FirstName()");
Property LastName As %String(POPSPEC = "LastName()");
Property CountOfThings As %Integer(POPSPEC = "Integer()");

ClassMethod OutputJSON() As %Status
{
If '..%ExistsId(1) Do ..Populate(100)
Set sql="select FirstName, LastName, CountOfThings from Atmus_Web_Test.JSONFromSQL"
Quit ##class(JSON.FromSQL).OutputJsonFromSQL(sql)
}

2. Utility Class

Class JSON.FromSQL Extends JSON.Base
{
ClassMethod OutputJsonFromSQL(
sql,
%parm...) As %Status
{
Write "["
Set rs=##class(%ResultSet).%New("%DynamicQuery:SQL")
$$$QuitOnError(rs.Prepare(sql))
$$$QuitOnError(rs.Execute(%parm...))
While rs.Next()
{
Write $Get(comma2),"{"
Set comma=""
For i=1:1:rs.GetColumnCount()
{
If (rs.GetData(i)?1.N),($e(rs.GetData(i))'="0")
{
Write $Get(comma),$c(34),rs.GetColumnName(i),$c(34),":",..EscapeJSON(rs.GetData(i))
}
Else
{
Write $Get(comma),$c(34),rs.GetColumnName(i),$c(34),":",$c(34),..EscapeJSON(rs.GetData(i)),$c(34)
}
Set comma=","
}
Write "}"
Set comma2=","
}
Write "]"
Quit $$$OK
}
ClassMethod GetJsonFromSQL(
sql,
%parm...) As %String
{
Set rs=##class(%ResultSet).%New("%DynamicQuery:SQL")
$$$QuitOnError(rs.Prepare(sql))
$$$QuitOnError(rs.Execute(%parm...))
Set out="["
While rs.Next()
{
Set out=out_$Get(comma2)_"{"
Set comma=""
For i=1:1:rs.GetColumnCount()
{
If (rs.GetData(i)?1.N),($e(rs.GetData(i))'="0")
{
Set out=out_$Get(comma)_$c(34)_rs.GetColumnName(i)_$c(34)_":"_..EscapeJSON(rs.GetData(i))
}
Else
{
Set out=out_$Get(comma)_$c(34)_rs.GetColumnName(i)_$c(34)_":"_$c(34)_..EscapeJSON(rs.GetData(i))_$c(34)
}
Set comma=","
}
Set out=out_"}"
Set comma2=","
}
Set out=out_"]"
Quit out
}

ClassMethod GetJsonFromResultSet(rs As %ResultSet) As %String
{
Set out="["
While rs.Next()
{
Set out=out_$Get(comma2)_"{"
Set comma=""
For i=1:1:rs.GetColumnCount()
{
If (rs.GetData(i)?1.N),($e(rs.GetData(i))'="0")
{
Set out=out_$Get(comma)_$c(34)_rs.GetColumnName(i)_$c(34)_":"_..EscapeJSON(rs.GetData(i))
}
Else
{
Set out=out_$Get(comma)_$c(34)_rs.GetColumnName(i)_$c(34)_":"_$c(34)_..EscapeJSON(rs.GetData(i))_$c(34)
}
Set comma=","
}
Set out=out_"}"
Set comma2=","
}
Set out=out_"]"
Quit out
} }

3. Base Class
Class JSON.Base Extends %RegisteredObject
{
ClassMethod EscapeJSON(string As %String) As %String
{
//TODO: this can be made more efficient!
Set string=$Replace(string,"\","\\")
Set string=$Replace(string,"/","\/")
Set string=$Replace(string,"""","\""")
Set string=$Replace(string,$C(8),"\b")
Set string=$Replace(string,$C(9),"\t")
Set string=$Replace(string,$C(10),"\r")
Set string=$Replace(string,$C(12),"\f")
Set string=$Replace(string,$C(13),"\n")
Quit string
}
ClassMethod EscapeJSONStream(in As %GlobalCharacterStream) As %GlobalCharacterStream
{
Do in.Rewind()
Set out = ##Class(%GlobalCharacterStream).%New()
Set char = in.Read(1)
while char'=""
{
Set char=$Replace(char,"\","\\")
Set char=$Replace(char,"/","\/")
Set char=$Replace(char,"""","\""")
Set char=$Replace(char,$C(8),"\b")
Set char=$Replace(char,$C(9),"\t")
Set char=$Replace(char,$C(10),"\r")
Set char=$Replace(char,$C(12),"\f")
Set char=$Replace(char,$C(13),"\n")
Do out.Write(char)
Set char = in.Read(1)
}
Quit out
} }