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

Question

Question

Evgeny Shvarov · Feb 13, 2019

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

Discussion (12)1

Log in or sign up to continue

Kyle Baxter · Feb 13, 2019

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!!!

2 0

score 0 · Answer 1 · 2019-02-13T14:27:53-05:00

Evgeny Shvarov · Feb 13, 2019

Thanks, Kyle!

0 0

score 0 · Answer 2 · 2019-02-14T07:45:28-05:00

Kyle Baxter · Feb 14, 2019

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

0 0

score 0 · Answer 3 · 2019-02-14T08:15:39-05:00

Joe Gazillo · Feb 14, 2019

Hi Kyle

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

Joe

0 0

score 0 · Answer 4 · 2019-02-14T10:07:49-05:00

Evgeny Shvarov · Feb 14, 2019

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

0 0

score 0 · Answer 5 · 2019-02-14T10:08:33-05:00

Evgeny Shvarov · Feb 14, 2019

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

0 0

score 0 · Answer 6 · 2019-02-16T09:25:34-05:00

Eduard Lebedyuk · Feb 16, 2019

Any particular reason to prefer:

$system.Dictionary.comMemberKeyGet

over

$$$comMemberKeyGet

?

0 0

score 0 · Answer 7 · 2019-02-14T07:57:22-05:00

Evgeny Shvarov · Feb 14, 2019

You cannot, yes because of Accepted status.

0 0

score 0 · Answer 8 · 2019-02-16T12:28:05-05:00

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

score 0 · Answer 9 · 2019-02-14T08:18:05-05:00

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.

score 0 · Answer 10 · 2019-02-13T14:27:32-05:00

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

score 1 · Answer 11 · 2019-02-14T08:25:06-05:00

Still another option:

Class dc.test Extends %Persistent
{

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

ClassMethod Test()
{
  d ..%KillExtent()
  
  f l=$lb("apple","pear","plum"),$lb({"Name":"Kyle"}.%ToJSON(),{"Name":"Evgeny"}.%ToJSON()) {
    s t=..%New()
    d t.JSONData.InsertList(l)
    d t.%Save()
  }

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

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

}

Result:

USER>d ##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: