Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Transform and store a real column in COPY command on a flex table

I'm using fjsonparser to load a flex table. One of the JSON attributes has geo-cordinates seperated by comma. For example,

 

{
"gps": "89.674622, 143.205566"
"key1": "value1",
"key2": "value2"
}

 

 

I've defined two real columns in my flex table for storing the latitude and longitude values. Now I'd like to populate these columns while loading data into the flex table.

 

I'm using copy command like:

 

copy car_readings  from 'data.json' parser fjsonparser();

 

I've found some examples, where hardcoded values are specified for the real columns:

 

 

 copy car_readings(__raw__, "lat" as 'blah'::varchar)  from 'data.json' parser fjsonparser();

 But couldn't find any reference for a computed value. Ultimately I'd like to parse this string "gps": "89.674622, 143.205566" and populate the columns latitude and longitude

 

Any suggestions?

 

 

 

Comments

  • Found the answer. Use MAPLOOKUP

     

    copy car_readings(__raw__, "lat" as split_part(MAPLOOKUP(__raw__, 'lat')::varchar, ',', 1)::numeric)  from 'data.json' parser fjsonparser();

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.