Simplest way to load a JSON with array into a regular table
atomix
✭
Hello,
I would like to load, parse and store the following json file into a regular table:
{ "movements": [ { "ID": "1", "ts": "2019-08-08T09:54:22.000-0500", "location": { "latitude": 30.379, "longitude": -86.32 } }, { "ID": "2", "ts": "2019-08-08T10:54:20.000-0400", "location": { "latitude": 28.50, "longitude": -81.42 } }, { "ID": "3", "ts": "2019-08-08T10:55:20.000-0400", "location": { "latitude": 28.50, "longitude": -81.426 } } ] }
Questions:
How do I COPY this efficiently into a regular table that maps all fields and results with the 3 rows in the table?
CREATE TABLE movements( id int, ts timestamp, latitude float, longitude float);
Thank you for any help/suggestion.
Tagged:
0
Comments
Don't want to use Flex tables? Well, okay.
Modified your DDL a little bit.
-
-
If you don't want to modify your DDL but don't mind a little more complexity to your COPY command, the following also works:
-
-
This is awesome, didn't realize the start_poin parameter. Thanks a million for your help!
Something is not right in the second option, if the "location.latitude" column is not in the table, and it's referred to in the COPY command:
I get an ERROR that it does not exist
it worked thanks