Evgeny Shvarov · Feb 13, 2019

How to return JSON Array in SQL request for a List Property?

Hi Community!

Suppose I have a property in a ObjectScript class:

Property values As list Of %String

What an SQL query can help to return the values of the property as JSON array via SQL access (JDBC)?

1 776
Discussion (12)3
Log in or sign up to continue

Class Def

Class User.JSONify Extends %Persistent


Property JSONData as list of %String;


ClassMethod MakeJSONArray(list) as %DynamicArray [sqlname="MakeJSONArray", sqlproc]


                q:(('$listvalid(list))|| (list="")) ""

                set jsonarray=[]

                for i=1:1:$LL(list)


                                d jsonarray.%Push($ListGet(list,i))


                q jsonarray




Data Population

USER>s obj=##class(User.JSONify).%New()


USER>s json="{""Name"":""Kyle""}"


USER>w obj.JSONData.Insert(json)


USER>s json="{""Name"":""Evgeny""}"


USER>w obj.JSONData.Insert(json)   


USER>w obj.%Save()




3.            select JSONData,MakeJSONArray(JSONData) from JSONify


JSONData             Expression_2

$lb("{""Name"":""Kyle""}","{""Name"":""Evgeny""}")           17@%Library.DynamicArray


1 Rows(s) Affected

statement prepare time(s)/globals/lines/disk: 0.0507s/37476/174342/0ms

          execute time(s)/globals/lines/disk: 0.0003s/2/598/0ms


Extra Proof
USER>s sql="select JSONData,MakeJSONArray(JSONData) from JSONify"


USER>s rs=##class(%SQL.Statement).%ExecDirect(,sql)


USER>w rs.%Next()


USER>s json=rs.%GetData(2)


USER>w json


USER>w json.%Get(0)


USER>w json.%Get(1)


Not 100% on this design, but getting it to work isn’t bad.  You’ll need extra error checking crap in any REAL code, obviously (for instance, I do ZERO checking to see if what is in the list is valid JSON).



Edit: Adding parens around list="" as is proper when writing ObjectScript code.  Due to ObjectScript's left-to-right evaluation, it is important to always wrap your conditions in parens or risk getting some unexpected results!!!

Can you not edit answers that are "accepted"?  Because my code is wrong and  needs updating.

Hi Kyle 

Thanks for the great article. What version of Cache will this code work? 


But why "minus"? This is elegant and works.

Thank you, Vitaly! ObjectScript is full of opportunities! With a creative community, of course :)

Any particular reason to prefer:





You cannot, yes because of Accepted status. 

The presence of a potential opportunity to read the documentation via "Class Reference".
For macros from *.inc this is not possible.

Joe Gazillo!  Good to hear from you!

I think that code should work back to 2016.2.  Basically after the change to our JSON objects.  To work on 2016.1 you'd have to change %Push() to $push().  Earlier versions, you'd have to something with the %ZEN.Auxiliary.jsonProvider to get that to work.

Also, the option which worked:

ClassMethod valuesCollectionToDisplay(val, delim) As %String
  quit ..AsJSON(val)

ClassMethod AsJSON(val) As %String
  set res = []
  set ptr = 0
  while $LISTNEXT(val, ptr, el) {
    do res.%Push(el)
  quit res.%ToJSON()

And the SQL call:

Select %EXTERNAL(values) values from data

Still another option:

Class dc.test Extends %Persistent

Property JSONData As list Of %String SqlListType = SUBNODE ];

ClassMethod Test()
  l=$lb("apple","pear","plum"),$lb({"Name":"Kyle"}.%ToJSON(),{"Name":"Evgeny"}.%ToJSON()) {

  zw @$system.Dictionary.comMemberKeyGet($this,$$$cCLASSstorage,$$$nameDefaultStorageNameGet,$$$cSDEFdatalocation)
  rs=##class(%SQL.Statement).%ExecDirect(,"select distinct json_arrayagg(JSONData %foreach(test)) from dc.test_JSONData")
  while rs.%Next() {

    !,jsonStr,", %Size()=",json.%Size(),!



["apple","pear","plum"], %Size()=3
[{"Name":"Kyle"},{"Name":"Evgeny"}], %Size()=2