New post
Question
· Sep 5, 2017

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.

Discussion (5)1
Log in or sign up to continue
Julius Kavay · Sep 5, 2017 
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))

3 0
Dhaval Shah · Jan 19, 2019

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

0 0