Written by

Senior Startups and Community Programs Manager at InterSystems Corporation

TEAM

Question Evgeny Shvarov · Feb 13, 2019

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

#Caché #InterSystems IRIS #JSON #SQL

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

Add reply

Comments

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)

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

USER>w obj.JSONData.Insert(json)

USER>w obj.%Save()

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

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

0 0

Evgeny Shvarov Feb 13, 2019 to Kyle Baxter

Thanks, Kyle!

0 0

Kyle Baxter Feb 14, 2019 to Kyle Baxter

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

0 0

Joe Gazillo Feb 14, 2019 to Kyle Baxter

Hi Kyle

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

Joe

0 0

Evgeny Shvarov Feb 14, 2019 to Evgeny Shvarov

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

0 0

Evgeny Shvarov Feb 14, 2019 to Vitaliy Serdtsev

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

0 0

Eduard Lebedyuk Feb 16, 2019 to Vitaliy Serdtsev

Any particular reason to prefer:

$system.Dictionary.comMemberKeyGet

over

$$$comMemberKeyGet

0 0

Evgeny Shvarov Feb 14, 2019 to Kyle Baxter

You cannot, yes because of Accepted status.

0 0

Vitaliy Serdtsev Feb 16, 2019 to Eduard Lebedyuk

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

0 0

Kyle Baxter Feb 14, 2019 to Joe Gazillo

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.

0 0

Evgeny Shvarov · Feb 13, 2019

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

0 0

Vitaliy Serdtsev · Feb 14, 2019

Still another option:

<FONT COLOR="#000080">Class dc.test Extends %Persistent
</FONT><FONT COLOR="#000000">{
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">JSONData </FONT><FONT COLOR="#000080">As list Of %String </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">SqlListType </FONT><FONT COLOR="#000000">= SUBNODE ];
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">l</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"apple"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"pear"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"plum"</FONT><FONT COLOR="#000000">),</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#ff00ff">{</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"Kyle"</FONT><FONT COLOR="#ff00ff">}</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%ToJSON</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#ff00ff">{</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"Evgeny"</FONT><FONT COLOR="#ff00ff">}</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%ToJSON</FONT><FONT COLOR="#000000">()) </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">t</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">t</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">JSONData</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">InsertList</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">l</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">t</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#800080">}
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">@</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.Dictionary</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">comMemberKeyGet</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$this</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$$$cCLASSstorage</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$$$nameDefaultStorageNameGet</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$$$cSDEFdatalocation</FONT><FONT COLOR="#000000">)
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"select distinct json_arrayagg(JSONData %foreach(test)) from dc.test_JSONData"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">while </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Next</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">jsonStr</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%GetData</FONT><FONT COLOR="#000000">(1),
      </FONT><FONT COLOR="#800000">json</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#ff00ff">[]</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%FromJSON</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">jsonStr</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!,</FONT><FONT COLOR="#800000">jsonStr</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">", %Size()="</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">json</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Size</FONT><FONT COLOR="#000000">(),!
  </FONT><FONT COLOR="#800080">}
</FONT><FONT COLOR="#000000">}
}</FONT>

Result:

USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">dc.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT>
<FONT COLOR="#000000">^dc.testD=2
</FONT><FONT COLOR="#000000">^dc.testD(1)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#000000">^dc.testD(1,</FONT><FONT COLOR="#008000">"JSONData"</FONT><FONT COLOR="#000000">)=3
</FONT><FONT COLOR="#000000">^dc.testD(1,</FONT><FONT COLOR="#008000">"JSONData"</FONT><FONT COLOR="#000000">,1)=</FONT><FONT COLOR="#008000">"apple"
</FONT><FONT COLOR="#000000">^dc.testD(1,</FONT><FONT COLOR="#008000">"JSONData"</FONT><FONT COLOR="#000000">,2)=</FONT><FONT COLOR="#008000">"pear"
</FONT><FONT COLOR="#000000">^dc.testD(1,</FONT><FONT COLOR="#008000">"JSONData"</FONT><FONT COLOR="#000000">,3)=</FONT><FONT COLOR="#008000">"plum"
</FONT><FONT COLOR="#000000">^dc.testD(2)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#000000">^dc.testD(2,</FONT><FONT COLOR="#008000">"JSONData"</FONT><FONT COLOR="#000000">)=2
</FONT><FONT COLOR="#000000">^dc.testD(2,</FONT><FONT COLOR="#008000">"JSONData"</FONT><FONT COLOR="#000000">,1)=</FONT><FONT COLOR="#008000">"{""Name"":""Kyle""}"
</FONT><FONT COLOR="#000000">^dc.testD(2,</FONT><FONT COLOR="#008000">"JSONData"</FONT><FONT COLOR="#000000">,2)=</FONT><FONT COLOR="#008000">"{""Name"":""Evgeny""}"</FONT>
<FONT COLOR="#ff00ff">[</FONT><FONT COLOR="#008000">"apple"</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"pear"</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"plum"</FONT><FONT COLOR="#ff00ff">]</FONT>, %Size()=3
<FONT COLOR="#ff00ff">[{</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"Kyle"</FONT><FONT COLOR="#ff00ff">}</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#ff00ff">{</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"Evgeny"</FONT><FONT COLOR="#ff00ff">}]</FONT>, %Size()=2

Doc:

0 0