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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file