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

  • 0
  • 1
  • 231
  • 9
  • 3

Answers

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)

1

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

 

USER>w obj.JSONData.Insert(json)   

1

USER>w obj.%Save()

1


-----------------
Query

-----------------

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()

1

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

 

USER>w json

16@%Library.DynamicArray

USER>w json.%Get(0)

{"Name":"Kyle"}

USER>w json.%Get(1)

{"Name":"Evgeny"}


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).

Cheers,

Kyle

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? 

Joe

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()
{
  ..%KillExtent()
  
  l=$lb("apple","pear","plum"),$lb({"Name":"Kyle"}.%ToJSON(),{"Name":"Evgeny"}.%ToJSON()) {
    t=..%New()
    t.JSONData.InsertList(l)
    t.%Save()
  }

  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=rs.%GetData(1),
      json=[].%FromJSON(jsonStr)

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

}

Result:

USER>##class(dc.test).Test()
^dc.testD=2
^dc.testD(1)=$lb("")
^dc.testD(1,"JSONData")=3
^dc.testD(1,"JSONData",1)="apple"
^dc.testD(1,"JSONData",2)="pear"
^dc.testD(1,"JSONData",3)="plum"
^dc.testD(2)=$lb("")
^dc.testD(2,"JSONData")=2
^dc.testD(2,"JSONData",1)="{""Name"":""Kyle""}"
^dc.testD(2,"JSONData",2)="{""Name"":""Evgeny""}"
 
["apple","pear","plum"], %Size()=3
 
[{"Name":"Kyle"},{"Name":"Evgeny"}], %Size()=2

Doc:

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

Any particular reason to prefer:

$system.Dictionary.comMemberKeyGet

over

$$$comMemberKeyGet

?

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