Written by

STAFF

Question John Kumpf · Apr 12, 2019

How do I return JSON from a database SQL call?

#Caché #JSON #REST API #SQL

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

Add reply

Comments

Vitaliy Serdtsev · Apr 13, 2019

Here is a small example:

<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">JSON_ARRAYAGG</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#808000">JSON_OBJECT</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'ID'</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">ID</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'Age'</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">Age</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'SSN'</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">SSN</FONT><FONT COLOR="#000000">)) </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">top </FONT><FONT COLOR="#000000">3 </FONT><FONT COLOR="#008000">ID</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">Age</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">SSN </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">sample</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">person</FONT><FONT COLOR="#000000">)</FONT>

Result:

<FONT COLOR="#ff00ff">[{</FONT><FONT COLOR="#008000">"ID"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">1</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">59</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"SSN"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"502-68-5767"</FONT><FONT COLOR="#ff00ff">}</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#ff00ff">{</FONT><FONT COLOR="#008000">"ID"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">2</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">6</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"SSN"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"169-66-9969"</FONT><FONT COLOR="#ff00ff">}</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#ff00ff">{</FONT><FONT COLOR="#008000">"ID"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">3</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">64</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"SSN"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"868-61-3642"</FONT><FONT COLOR="#ff00ff">}]</FONT>

0 0

John Kumpf Apr 16, 2019 to Vitaliy Serdtsev

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.Pag…

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 to John Kumpf

See examples and try "Run It" in JSON_ARRAYAGG.

E.g.:

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"]

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"}

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"}]

0 0

John Kumpf Apr 16, 2019 to Vitaliy Serdtsev

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.

0 0

Vasiliy Bondar Jun 12, 2019 to John Kumpf


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 " ?

0 0