Resultset to JSON

Question

Question

Nezla · May 3, 2022

#Ensemble

Hi Guys,

How do I convert a Query resultset to JSON file ?

Thanks

Product version: Ensemble 2014.1

Discussion (4)1

Log in or sign up to continue

score 0 · Answer 1 · 2022-05-03T12:01:08-04:00

You can build a %Library.DynamicObject or a %Library.DynamicArray (both extend the %Library.DynamicAbstractObject class) from the ResultSet and use the %ToJSON method.
To build the object it will be necessary to loop the ResultSet writing line by line.

score 0 · Answer 2 · 2022-05-03T13:55:40-04:00

In Ensemble 2014, which the author indicated in his question, there are no classes %Library.DynamicObject and %Library.DynamicArray

score 0 · Answer 3 · 2022-05-04T06:58:37-04:00

See %ZEN.Auxiliary.jsonSQLProvider:%WriteJSONStreamFromSQL()

Simple example

Class dc.test Extends %Persistent
{

Property int As %Integer;

Property str As %VarString;

Property bool As %Boolean;

Property Date As %Date;

ClassMethod Test()
{
  d ..%KillExtent()
  
  &sql(insert into dc.test("int",str,bool,"Date")
        select 30,'Hello Caché',0,current_date
        union
        select 303,'ăîşţâ',1,0
        union
        select null,null,null,null)
  
  
  s stream=##class(%Stream.FileBinary).%New(),
    stream.Filename="c:\temp\test.json",
  
    sql="select * from dc.test",
    format="twu",
    maxRows=0

  s status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONStreamFromSQL(.stream, sql, format, maxRows)  

  d:$$$ISOK(status) stream.%Save()
}

}

The contents of the file test.json (UTF8):

{
"children":[
{"ID":1,"Date":"04.05.2022","bool":0,"int":30,"str":"Hello Caché"}
,{"ID":2,"Date":"31.12.1840","bool":1,"int":303,"str":"ăîşţâ"}
,{"ID":3,"Date":"","bool":"","int":"","str":""}
]
}

score 0 · Answer 4 · 2022-10-20T13:06:25-04:00

Michael Davidovich · Oct 20, 2022

Can you go the opposite way? Bind a JSON object to a %Library.ResultSet?

0 0