We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Parse elements in JSON list (Flex Table) — Vertica Forum

Parse elements in JSON list (Flex Table)

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

 

Comments

  • I managed to solve it myself using the mapItems function. 

     

    - Derek

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file