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?





  • Options

    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file