Parse elements in JSON list (Flex Table)

DerekW

Hi,

 

I have a flex table with JSON event data. One event type has a column which contains a JSON list (stored as BINARY). This column contains a list of uids which i need to parse to rows.

 

I can use the maptostring() function to transform the BINARY to a readable string, e.g.:

select
  maptostring ("body.participantids")
from
  flextable
limit 1

 

This results in one row with one big string like here:

 

{
"0" : "7ed8c27d-6ab0-4ad5-9888-8e56698bd7d2",
"1" : "f0fc02b9-f5c3-4495-a3d9-5be5fb5dc433",
"2" : "3ec2527e-66da-40d5-9184-6b460813f779"
}

 

Now, i need to convert this in such a way so that i get a row for each uid. Any ideas how to do this?

 

FYI, this list can grow to >10000 uids, so i prefer not to flatten it.

 

Thanks!

 

- Derek

 

DerekW

I managed to solve it myself using the mapItems function. 

 

- Derek