Simplest way to load a JSON with array into a regular table

atomix · August 2019

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.

LenoyJ · August 2019

Don't want to use Flex tables? Well, okay.

Modified your DDL a little bit.

dbadmin=> CREATE TABLE movements( id int, ts timestamp, "location.latitude" float, "location.longitude" float);
CREATE TABLE

-

dbadmin=> COPY movements FROM '/home/dbadmin/atomix.json' PARSER fjsonparser(start_point='movements');
 Rows Loaded
-------------
           3
(1 row)

-

dbadmin=> select * from movements;
 id |         ts          | location.latitude | location.longitude
----+---------------------+-------------------+--------------------
  3 | 2019-08-08 10:55:20 |              28.5 |            -81.426
  2 | 2019-08-08 10:54:20 |              28.5 |             -81.42
  1 | 2019-08-08 10:54:22 |            30.379 |             -86.32
(3 rows)

LenoyJ · August 2019

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:

dbadmin=> CREATE TABLE movements( id int, ts timestamp, latitude float, longitude float);
CREATE TABLE

-

dbadmin=> COPY movements(id,ts, "location.latitude" FILLER float, "location.longitude" FILLER float, latitude as "location.latitude",longitude as "location.longitude")  FROM '/home/dbadmin/atomix.json' PARSER fjsonparser(start_point='movements');
 Rows Loaded
-------------
           3
(1 row)

-

dbadmin=> select * from movements;
 id |         ts          | latitude | longitude
----+---------------------+----------+-----------
  3 | 2019-08-08 10:55:20 |     28.5 |   -81.426
  1 | 2019-08-08 10:54:22 |   30.379 |    -86.32
  2 | 2019-08-08 10:54:20 |     28.5 |    -81.42
(3 rows)

atomix · August 2019

This is awesome, didn't realize the start_poin parameter. Thanks a million for your help!

elghali · October 2021

@LenoyJ said:
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:

dbadmin=> CREATE TABLE movements( id int, ts timestamp, latitude float, longitude float);
CREATE TABLE

-

dbadmin=> COPY movements(id,ts, "location.latitude" FILLER float, "location.longitude" FILLER float, latitude as "location.latitude",longitude as "location.longitude")  FROM '/home/dbadmin/atomix.json' PARSER fjsonparser(start_point='movements');
 Rows Loaded
-------------
           3
(1 row)

-

dbadmin=> select * from movements;
 id |         ts          | latitude | longitude
----+---------------------+----------+-----------
  3 | 2019-08-08 10:55:20 |     28.5 |   -81.426
  1 | 2019-08-08 10:54:22 |   30.379 |    -86.32
  2 | 2019-08-08 10:54:20 |     28.5 |    -81.42
(3 rows)

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:

"location.latitude" FILLER float

I get an ERROR that it does not exist

UPDATE:

it worked thanks

Simplest way to load a JSON with array into a regular table

Comments

Leave a Comment