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_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.