Resultset to JSON
Hi Guys,
How do I convert a Query resultset to JSON file ?
Thanks
Product version: Ensemble 2014.1
Discussion (4)0
Comments
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.
In Ensemble 2014, which the author indicated in his question, there are no classes %Library.DynamicObject and %Library.DynamicArray
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":""}
]
}Can you go the opposite way? Bind a JSON object to a %Library.ResultSet?