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

atomixatomix
edited August 2019 in General Discussion

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.

Comments

  • LenoyJLenoyJ - Select Field - Employee
    edited 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)
    
  • LenoyJLenoyJ - Select Field - Employee

    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)
    
  • This is awesome, didn't realize the start_poin parameter. Thanks a million for your help!

  • elghalielghali
    edited 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

Leave a Comment

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