How do I return JSON from a database SQL call?
Referencing this post:
https://community.intersystems.com/post/producing-json-sql
I'm not sure how to actually interact with the result set I get from doing something like this. I want to return something like:
[{"field1":1, "field2":2}, {"field1":2, "field2":10}]
I'm finding it very difficult to get it in this format, since %Print appends a newline onto the end of the {} object it prints.
Here's the closest I've gotten:
set query = "select JSON_OBJECT('field1': field1, 'field2":field2) from MyTable where x=? and y=?"
set tStatement = ##class(%SQL.Statement).%New()
set qStatus = tStatement.%Prepare(query)
if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
write "["
set rset = tStatement.%Execute(queryParams...)
while rset.%Next() {
do rset.%Print(" ^ ")
write ","
}
write "]"
return 1
Thanks.