@Rocky in a json stream load, I need to define columns because my target table has a few default fields. My objects can vary a bit, what happens if I list columns that aren't present in the json object?

{thought According to the documentation: • "For missing columns, if the column definition is nullable, then

NULL

will be added, or the default value can be added by

ifnull

." • It specifically mentions using

ifnull

in the

columns

header to handle cases where a field is missing in the JSON data. • If a field is missing in JSON and the table column is

NOT NULL

, StarRocks "automatically fills a NULL value", which usually results in an error/filtered row in strict mode or if the column cannot be null. • Crucially, if you specify a column in the

columns

/

jsonpaths

mapping, and that column is missing from the JSON object, it is treated as a

NULL

value. • If the table has a

DEFAULT

value defined in the schema, this

DEFAULT

is only automatically used if the column is omitted from the

columns

list (or if the source value is not provided at all and not mapped). • If the column is listed in the mapping, but the JSON object doesn't have it, the mapping evaluates to

NULL

. The

NULL

then attempts to enter the column. If the user wants the table's default, they should use

ifnull

in the mapping. However, there's a nuance: if the column is NOT in the

columns

list, it will naturally take the table's default value. Wait, let's re-read the "Import Example-1" and "Import Example-2" in

Json_loading.md

. Example 1 (Simple mode): JSON

{"k1": 2}

. Table has

k1, k2

. Result:

k1=2, k2=NULL

. Example 2 (Matched mode): JSON

{"k1": 2}

.

columns: k1, tmp_k2, k2 = ifnull(tmp_k2, 'x')

. Result:

k1=2, k2='x'

. If the table definition for

k2

was

VARCHAR(10) DEFAULT 'default_val'

, and we used Simple Mode, would it be

NULL

or

'default_val'

? In SQL

INSERT

statements, omitting a column uses the default. In Stream Load simple mode, columns not present in JSON are treated as missing. The doc says "if the column definition is nullable, then

NULL

will be added". This suggests it doesn't automatically fall back to the table default if the key is missing from JSON. Wait, if I have

col_a INT DEFAULT 100

and I load

{"col_b": 1}

. If I don't map

col_a

, does it get 100? Actually, if the column is completely omitted from the load metadata (i.e., not in the

columns

list), StarRocks will use the table's default value for that column for every row. But the user says "My objects can vary a bit, what happens if I list columns that aren't present in the json object?". If they list them in the mapping, they get

NULL

. If the table has a default, it won't be used because the mapping is explicitly providing a

NULL

. So the answer is: 1. If listed in

columns

(and

jsonpaths

), a missing key in JSON results in a

NULL

value for that column. 2. If the column is

NOT NULL

in the table, the row will fail/be filtered (depending on

strict_mode

). 3. If the user wants to use a default value for missing JSON keys, they should use the

ifnull(col, 'default')

syntax in the

columns

header. 4. If they want to use the table's defined

DEFAULT

, they must ensure they are NOT providing a value for it in the mapping, but that's hard if the column is sometimes present and sometimes not. 5. If the JSON objects vary (some have the key, some don't), and the user wants the table default to be used when missing, they can't easily do it by just listing the column because the listing overrides with the source value (which is

NULL

if missing). Let's double check if StarRocks has a way to "fall back to table default if source is null" during load. Usually, this is done via

col = ifnull(col, 'default_value_literal')

. You have to provide the literal default value again in the load command. Wait, does StarRocks support

DEFAULT

keyword in the mapping? Probably not. One more thing:

partial_update

? No, that's for Primary Key tables and has different semantics. I will formulate the answer based on: • Missing keys in JSON mapping evaluate to

NULL

.