Skip to main content

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

  • August 9, 2019
  • 4 replies
  • 5 views
atomix
Forum|alt.badge.img+1
  • atomix
  • Participating Frequently

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.

    4 replies

    LenoyJ
    Forum|alt.badge.img+1
    • LenoyJ
    • Participating Frequently
    • August 9, 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
    Forum|alt.badge.img+1
    • LenoyJ
    • Participating Frequently
    • August 9, 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
    Forum|alt.badge.img+1
    • atomix
    • Author
    • Participating Frequently
    • August 9, 2019

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

    elghali
    Forum|alt.badge.img+1
    • elghali
    • Participating Frequently
    • October 28, 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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement