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.

  • + 1
  • 3
  • 271
  • 4
  • 1

Answers

Here is a small example:

select JSON_ARRAYAGG(JSON_OBJECT('ID':ID,'Age':Age,'SSN':SSN)) from (select top ID,Age,SSN from sample.person)

Result:

[{"ID":1,"Age":59,"SSN":"502-68-5767"},{"ID":2,"Age":6,"SSN":"169-66-9969"},{"ID":3,"Age":64,"SSN":"868-61-3642"}]

Thanks a lot.  This worked.

I found the page about this function for anyone who sees this later:

https://irisdocs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page...

Would you mind explaining how exactly this particular line is working?  Why do you have to use both JSON_ARRAYARG and JSON_OBJECT and what does that syntax mean?  It looks like it's essentially "selecting" the data from one result set into another as JSON, but I'm not sure how.  What does the JSON_OBJECT call do here, and what does the JSON_ARRAYAGG call do?

See examples and try "Run It" in JSON_ARRAYAGG.

E.g.:

  1. SELECT JSON_ARRAYAGG(Home_StateFROM Sample.Person WHERE Home_State %STARTSWITH 'A'

    Result:

    ["AR","AL","AR","AL","AL","AR","AK","AL","AR","AK","AK","AZ","AR","AR","AL"]
  2. SELECT JSON_OBJECT('state':Home_StateFROM Sample.Person WHERE Home_State %STARTSWITH 'A'

    Result:

    {"state":"AR"}
    {"state":"AL"}
    {"state":"AR"}
    {"state":"AL"}
    {"state":"AL"}
    {"state":"AR"}
    {"state":"AK"}
    {"state":"AL"}
    {"state":"AR"}
    {"state":"AK"}
    {"state":"AK"}
    {"state":"AZ"}
    {"state":"AR"}
    {"state":"AR"}
    {"state":"AL"}
  3. SELECT JSON_ARRAYAGG(JSON_OBJECT('state':Home_State)) FROM Sample.Person WHERE Home_State %STARTSWITH 'A'

    Result:

    [{"state":"AR"},{"state":"AL"},{"state":"AR"},{"state":"AL"},{"state":"AL"},{"state":"AR"},{"state":"AK"},{"state":"AL"},{"state":"AR"},{"state":"AK"},{"state":"AK"},{"state":"AZ"},{"state":"AR"},{"state":"AR"},{"state":"AL"}]

Oh okay, I see.  JSON_ARRAYAGG takes whatever results it gets back, and puts them into a JSON array.  Since JSON_OBJECT returns a bunch of results that are json objects, using JSON_ARRAYAGG(JSON_OBJECT( says to take all those json objects, and put them into a json array.

As far as the non select part of the query, it looks like you can specify it with a new "select", or without.  I.e.:

SELECT JSON_ARRAYAGG(JSON_OBJECT('state':Home_State)) FROM Sample.Person WHERE Home_State %STARTSWITH 'A'
SELECT JSON_ARRAYAGG(JSON_OBJECT('state':Home_State)) FROM (select state from Sample.Person WHERE Home_State %STARTSWITH 'A')

Thanks a lot for taking the time to explain.  It's very clear to me what is happening now.


select JSON_ARRAYAGG(JSON_OBJECT('ID':ID,'Aka':Aka)) from (select top 2 ID,Aka from ent.Buyer)
["{\"ID\":1,\"Aka\":\"+7(000)0000000\"}","{\"ID\":2,\"Aka\":\"+7(962)5020828\"}"]

IRIS return with Escaped Characters. How to remove \ and " ?