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