How to convert collection properties into json array?

Caché, SQL

Let's say I have this property:

Property FavoriteColors As List Of %String;

I heed to convert it to JSON using SQL or at least without object access (so direct global access).

What's the fastest way to do that?

I thought about JSON_ARRAY and JSON_ARRAYAGG sql functions but they don't do that.

  • 0
  • 0
  • 621
  • 4
  • 1

Answers

Class your.class
{
Property Colors As list Of %String;


ClassMethod toJson(list) As %String [ SqlProc ]
{
    s json=""
    f i=1:1:$ll(list) {
        s:i>1 json=json_"," s item=$lg(list,i)
        if '$ld(list,i) { s json=json_"null" }
        elseif item="" { s json=json_"""""" }
        elseif $lv(item) { s json=json_..toJson(item) }
        elseif $num(item,".")=item { s json=json_$fn(item,"N") }
        else {
            f c="\","/","""",$c(8),$c(9),$c(10),$c(12),$c(13) s item=$replace(item,c,"\"_$tr(c,$c(8,9,10,12,13),"btnfr"))
            f  q:'$locate(item,"[:cntrl:]",0,j,v)  s $e(item,j-1)="\u"_$e($zh($a(v)+65536),2,5)
            s json=json_""""_item_""""
        }
    }
    
    q "["_json_"]"
}
}

select your.class_toJson(Colors) from your.class

gives you the expected result . If you prefer  a direct  use of globals, then use

write ##class(your.class).toJson($lg(^your.global(theOID),theSlotNumber))

In the 'else' branch you could also use $ZConvert(item,"O","JSON")

Thanks @Julius Kavay

for above method

I used above method from Terminal I get proper result.
["KY","TN","AL","GA"]
But from Code I am getting

["\"KY\",\"TN\",\"AL\",\"GA\""]

Why is that so? How can I get same result as I get by running Terminal.

What do you mean with "from Code I am getting"? From which Code?
["\"KY\",\"TN\",\"AL\",\"GA\""] seems to be the same as the terminal output, except the quote chars are escaped...

@Julius Kavay 

Can you please help me in fixing this issue.

As you said it is similar to terminal Output but quote chars are escaped.

we have a GET call where in we need to send Data in JSOn Format.
For Property States of My Class which is type of List of %String

'States': JSON_ARRAY((SELECT MyClass_toJson(ServiceRegions_States) from MyClass)

I get output as I shared above.