Written by

Senior Cloud Architect at InterSystems

STAFF

Question Eduard Lebedyuk · 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)0

Add reply

Comments

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

0 0

Herman Slagman Sep 6, 2017 to Julius Kavay

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

0 0

Dhaval Shah Jan 17, 2019 to Julius Kavay

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.

0 0

Julius Kavay Jan 17, 2019 to Dhaval Shah

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

0 0

Dhaval Shah Jan 19, 2019 to Julius Kavay

@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