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

KarthikM

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?

 

 

 

KarthikM

Found the answer. Use MAPLOOKUP

 

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