Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Differing behavior for fjsonparser and regular loads to table with not null column

I'm loading json into a regular, non-flex table using fjsonparser. Table looks like this:

 

CREATE TABLE TEMP.json_transform_test (
real_column VARCHAR(80),
real_column2 VARCHAR(80) NOT NULL DEFAULT 'default'
);

 

and the copy statement looks like this:

 

copy temp.json_transform_test (real_column, real_column2) FROM LOCAL 'test.json' parser fjsonparser();

 

If the data does not contain the key real_column2, the entire COPY statement fails:

 

ERROR: COPY: Input record 1 has been rejected (Null value for NOT NULL column 2 (real_column2))

 

I'm surprised by this, because the equivalent:

 

copy temp.json_transform_test (real_column, real_column2) FROM LOCAL 'test.csv' DELIMITER ',';

 

does NOT fail if the second column is null, just rejects that specific row (unless ABORT ON ERROR is specified).

 

Is there a way to force the fjsonparser version to reject individual rows instead of failing the entire statement?

 

If no, alternatively, is there a good way to set to a default using FILLER in the case that key and column names match? In the csv case where I can name the input data at will, I'd do something like:

 

copy temp.json_transform_test (real_column, real_column2_raw FILLER VARCHAR(30), real_column2 as ifnull(real_column2_raw, 'default')) FROM LOCAL 'C:/users/bletson/desktop/test.csv' DELIMITER ',';

 

But that's not possible for the json case as real_column2_raw isn't the key name, real_column2 is.

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.