New post
Question
· Apr 12, 2019

How do I return JSON from a database SQL call?

#JSON #REST API #SQL #Caché

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.

Discussion (5)0
Log in or sign up to continue
John Kumpf · Apr 16, 2019

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?

0 0
Vitaliy Serdtsev · Apr 16, 2019

See examples and try "Run It" in JSON_ARRAYAGG.

E.g.:

  1. SELECT JSON_ARRAYAGG(Home_State) FROM 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_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"}
  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"}]
2 0
John Kumpf · Apr 16, 2019

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.

1 0