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)?
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!!!
Thanks, Kyle!
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
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:
over
?
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:
Result:
Doc: