Skipping Records with Unspecified JSON Fields

Posted June 26, 2018 by Phil Molea, Sr. Information Developer, Vertica

High angle view of Beijing Guomao.
Serge Bonte and Jim Knicely authored this post. Vertica provides a built-in file parser named FJSONPARSER that parses and loads a JSON file. This file can contain either repeated JSON data objects (including nested maps) or an outer list of JSON elements. For a flex table, the parser stores the JSON data in a single-value VMap. For a hybrid or columnar table, the parser loads data directly in any table column with a column name that matches a key in the JSON source data.

Load the JSON Data into Vertica

Let’s look at an example JSON file: dbadmin=> \! cat /home/dbadmin/some_data.json {"age": 5, "name": "Tim"} {"age": 7, "name": "Joe", "zip": 92022} {"age": 8} {"name" : "Jim"} First, you need to create a Vertica table to hold the contents of the JSON data: dbadmin=> CREATE TABLE some_data1(age INT, name VARCHAR); CREATE TABLE Copy the data from the JSON file into the new table: dbadmin=> COPY some_data1 FROM '/home/dbadmin/some_data.json' PARSER fjsonparser(); Rows Loaded ------------- 4 (1 row) dbadmin=> SELECT * FROM some_data1; age | name —–+—— 5 | Tim 8 | | Jim 7 | Joe (4 rows)

Skip the JSON Records if Field not Specified

To skip loading records when the value for a field is not specified, add a NOT NULL constraint to the corresponding table column. Vertica does not load that record into the table. Again, create a Vertica table. This time specify the NOT NULL constraint to the age column: dbadmin=> CREATE TABLE some_data2(age INT NOT NULL, name VARCHAR); CREATE TABLE Copy the JSON data into this table. Note that Vertica does not load the record that does not contain an age value the some_data2.json. dbadmin=> COPY some_data2 FROM '/home/dbadmin/some_data.json' PARSER fjsonparser(); Rows Loaded ------------- 3 (1 row) dbadmin=> SELECT * FROM some_data2; age | name —–+—— 5 | Tim 8 | 7 | Joe (3 rows)

For More Information

In the Vertica documentation, see: • Load JSON DataFJSONPARSER