The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Parse json file with Arrays

edited October 2018 in General Discussion

Hi,
I have a json file with below structure

{"Event":{"name":"John",
"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Square",
"City":"London",
"Zip":"456112"
}
{"Addr1":"4112-B",
"Addr2":"Madison Square",
"City":"Manchester",
"Zip":"125465"
}]
}
}

Expected Output:
311/A|Magure Square|London|456112
4112-B|Madison Square|Manchester|125465

I tried using a json parser I found developed in C which is not able to handle above request.
Can someone help me parse this json without flex tables?

Comments

  • chaimachaima Employee

    Hi,

    If I understood your problem correctly, you can achieve this using fjsonparser to load your data in a regular columnar table.
    Here is an example (btw there is a missing comma in the file otherwise it's not a valid json):
    dbadmin=> ! cat /tmp/json_table.json
    {"Event":{"name":"John",
    "Age":"29",
    "Address":[{"Addr1":"311/A",
    "Addr2":"Magure Square",
    "City":"London",
    "Zip":"456112"
    },
    {"Addr1":"4112-B",
    "Addr2":"Madison Square",
    "City":"Manchester",
    "Zip":"125465"
    }]
    }
    }
    dbadmin=> CREATE TABLE json_table(addr1 varchar(32), addr2 varchar(32), city varchar(32), zip integer);
    CREATE TABLE
    dbadmin=> copy json_table from '/tmp/json_table.json' WITH PARSER fJsonParser(start_point='Address');

    dbadmin=> select * from json_table;
     addr1  |     addr2      |    city    |  zip
    --------+----------------+------------+--------
     311/A  | Magure Square  | London     | 456112
     4112-B | Madison Square | Manchester | 125465
    (2 rows)
    

    Let me know if this helps,
    Chaima

  • You can use FJSONParser and tell the parser that you want to start parsing at a certain key, in your case 'Address' (the array that has the collection of addresses). FJSONParser equally loads data to columnar tables, not only to Flex tables. See the example below.

    create table t2(Addr1 varchar, Addr2 varchar, City varchar, Zip varchar);
    CREATE TABLE
    
    -- Note the use of start_point to tell the parser where to start loading data.
    -- Also unset flatten_maps to preserve nested data structure.
    copy t2 from stdin parser fjsonparser(flatten_maps=false, start_point='Address');
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> {"Event":{"name":"John",
     "Age":"29",
     "Address":[{"Addr1":"311/A",
     "Addr2":"Magure Square",
     "City":"London",
     "Zip":"456112"
     },
     {"Addr1":"4112-B",
      "Addr2":"Madison Square",
      "City":"Manchester",
      "Zip":"125465"
     }]
             }
    }
    >> >> >> >> >> >> >> >> >> >> >> >> >> >> \.
    
    select * from t2;
     Addr1  |     Addr2      |    City    |  Zip
    --------+----------------+------------+--------
     311/A  | Magure Square  | London     | 456112
     4112-B | Madison Square | Manchester | 125465
    (2 rows)
    
  • Hi Chaima,
    Thanks for the help. its working great :)

    But the issue is the table is not loaded with all the rows.
    Only the array present in the first row of json file are loaded to table.

    Below is the actual data:
    {"Event":{"name":"John",
    "Age":"29",
    "Address":[{"Addr1":"311/A",
    "Addr2":"Magure Square",
    "City":"London",
    "Zip":"456112"
    },
    {"Addr1":"4112-B",
    "Addr2":"Madison Square",
    "City":"Manchester",
    "Zip":"125465"
    }]
    }
    }
    {"Event":{"name":"Russell",
    "Age":"29",
    "Address":[{"Addr1":"311/A",
    "Addr2":"Magure Street",
    "City":"Liverpool",
    "Zip":"456345"
    },
    {"Addr1":"42-B",
    "Addr2":"Man Square",
    "City":"Manchester",
    "Zip":"125789"
    }]
    }
    }

    Records of Russell are not getting loaded to the table. Is there anything I can do run the parser for all the records in the file?

  • @Ariel_Cary I have tried your approach as well. its working well.
    But the elements of second row are not getting inserted to table.

    There is a new line char at the end of every record

  • mani5b8: Could you show us an example of what you mean by second row?
    I successfully loaded the two JSON records you posted in your previous example, i.e. 4 rows in total (ZIP codes are different).

    truncate table t2;
    TRUNCATE TABLE
    
    copy t2 from stdin parser fjsonparser(flatten_maps=false, start_point='Address');
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> {"Event":{"name":"John",
    "Age":"29",
    "Address":[{"Addr1":"311/A",
    "Addr2":"Magure Square",
    >> >> >> >> "City":"London",
    >> "Zip":"456112"
    },
    {"Addr1":"4112-B",
    "Addr2":"Madison Square",
    "City":"Manchester",
    "Zip":"125465"
    }]
    }
    }
    {"Event":{"name":"Russell",
    "Age":"29",
    "Address":[{"Addr1":"311/A",
    >> >> >> >> >> >> >> >> >> >> >> >> "Addr2":"Magure Street",
    "City":"Liverpool",
    "Zip":"456345"
    },
    {"Addr1":"42-B",
    "Addr2":"Man Square",
    "City":"Manchester",
    "Zip":"125789"
    }]
    }
    }
    >> >> >> >> >> >> >> >> >> >> >> \.
    
    select * from t2;
     Addr1  |     Addr2      |    City    |  Zip
    --------+----------------+------------+--------
     311/A  | Magure Square  | London     | 456112
     4112-B | Madison Square | Manchester | 125465
     311/A  | Magure Street  | Liverpool  | 456345
     42-B   | Man Square     | Manchester | 125789
    (4 rows)
    
  • @Ariel_Cary i ran the same query. But i am able to insert only first 2 records.

       => copy t2 from stdin parser fjsonparser(flatten_maps=false, start_point='Address');
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> {"Event":{"name":"John",
    >> "Age":"29",
    >> "Address":[{"Addr1":"311/A",
    >> "Addr2":"Magure Square",
    >> "City":"London",
    >> "Zip":"456112"
    >> },
    >> {"Addr1":"4112-B",
    >> "Addr2":"Madison Square",
    >> "City":"Manchester",
    >> "Zip":"125465"
    >> }]
    >> }
    }
    >> >> {"Event":{"name":"Russell",
    >> "Age":"29",
    >> "Address":[{"Addr1":"311/A",
    >> "Addr2":"Magure Street",
    >> "City":"Liverpool",
    >> "Zip":"456345"
    >> },
    >> {"Addr1":"42-B",
    >> "Addr2":"Man Square",
    >> "City":"Manchester",
    >> "Zip":"125789"
    >> }]
    }
    >> >> }
    >> \.
    => select * from t2;
     Addr1  |     Addr2      |    City    |  Zip
    --------+----------------+------------+--------
     311/A  | Magure Square  | London     | 456112
     4112-B | Madison Square | Manchester | 125465
    (2 rows)
    

    I want to load the same data present in a file.
    the the parser is copying the contents of first record only.

  • @Ariel_Cary I tried loading the same as you mentioned.

    => copy t2 from stdin parser fjsonparser(flatten_map
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.

    {"Event":{"name":"John",
    "Age":"29",
    "Address":[{"Addr1":"311/A",
    "Addr2":"Magure Square",
    "City":"London",
    "Zip":"456112"
    },
    {"Addr1":"4112-B",
    "Addr2":"Madison Square",
    "City":"Manchester",
    "Zip":"125465"
    }]
    }

    }

    {"Event":{"name":"Russell",

    "Age":"29",
    "Address":[{"Addr1":"311/A",
    "Addr2":"Magure Street",
    "City":"Liverpool",
    "Zip":"456345"
    },
    {"Addr1":"42-B",
    "Addr2":"Man Square",
    "City":"Manchester",
    "Zip":"125789"
    }]

    }

    }

    .

    => select * from t2;
    Addr1 | Addr2 | City | Zip
    --------+----------------+------------+--------
    311/A | Magure Square | London | 456112
    4112-B | Madison Square | Manchester | 125465
    (2 rows)

    I need to load this from a file.
    Parser is not loading only 2 records with file aswell.

  • Could you tell us what Vertica version you are using?

  • Vertica Analytic Database v9.1.1-8

  • That doesn't look like a correct Vertica version. Please double check:
    select version();

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.