How to convert SQL persistent classes to JSON output

How to convert SQL persistent classes to JSON output - I have tried 1 option but appears to be very lengthy process.

I have many other SQL storage's defined and have to expose them as well to JSON. Can anyone please suggest a better approach.

My column names usually match with my Json properties.

My User.TestClass is the class with GetJsonList() as my method to return /output JSON formatted data.

The Code tried and SQL storage structure are as follows -

Class User.TestClass Extends %RegisteredObject
{

ClassMethod GetJsonList() As %Status
{
Set SQL="SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData"
Set Statement= ##class(%SQL.Statement).%New()
Set Status= Statement.%Prepare(SQL)
Quit:$$$ISERR(Status) Status
Set tResults=Statement.%Execute()
Quit:$$$ISERR(Status) Status

List=##class(%Library.ListOfObjects).%New()
while tResults.%Next() {
tResponse=##class(User.Sample.Response).%New()
tResponse.ID=tResults.ID
tResponse.Column1=tResults.Column1
tResponse.Column2=tResults.Column2
tResponse.DataID=tResults.DataID
List.Insert(tResponse)
}
Stream=##class(%GlobalCharacterStream).%New()
Status=##class(Ens.Util.JSON).ObjectToJSONStream(List,.Stream)
While 'Stream.AtEnd Write Stream.ReadLine(,.sc,.eol) If $$$ISERR(sc) Write "Error" Quit If eol Write ! }
}

}
 

  • + 1
  • 0
  • 343
  • 1
  • 2

Answers

Are looking for this?

SELECT JSON_OBJECT('ID':ID,'Column1':COLUMN1,'Column2':COLUMN2,'DataID':DataID) FROM  SQLUser.SampleData

Cache for Windows (x86-64) 2017.2.1 (Build 801U)

Sorry I don't have your data to show your result

somewhat similar. 

SELECT top 3 JSON_OBJECT('name:':Name,'home':HOME_CITY,'dob':DOB) FROm Sample.Person

{"name:":"Smyth,Valery B.","home":"Hialeah","dob":"2001-01-06"}
{"name:":"Jones,Valery E.","home":"Islip","dob":"1959-11-11"}
{"name:":"Newton,David H.","home":"Youngstown","dob":"1969-12-01"}

Or all together:

SELECT '{"result":'||List(xx)||'}' FROM
(SELECT top 3 JSON_OBJECT('name:':Name,'home':HOME_CITY,'dob':DOB) xx
FROM Sample.Person)

{"result":
  {"name:":"Smyth,Valery B.","home":"Hialeah","dob":"01/06/2001"}
 ,{"name:":"Jones,Valery E.","home":"Islip","dob":"11/11/1959"}
 ,{"name:":"Newton,David H.","home":"Youngstown","dob":"12/01/1969"}
}
 

The difference in DOB is just display mode vs. ODBC mode 

Thank you Robert that did help me..
I also found these options below -

s Status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONFromSQL("json","SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData")
-- or --
    s Status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONStreamFromSQL(.pStream,"SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData")

    While 'pStream.AtEnd { Write pStream.ReadLine(,.sc,.eol) If $$$ISERR(sc) { Write "Error" Quit } If eol { Write ! } }

Check out RESTForms project - generic REST API backend for modern web applications (articles: part 1, part 2). It handles transformation of classes/queries to JSON and exposes them via REST.