Discussion: Should we write nulls into JSON columns that are expected to be arrays?

[hermanschaaf]

There are cases where JSON columns are expected to be arrays, and our policy queries then use JSONB_ARRAY_ELEMENTS to extract the elements. However, if the returned value from AWS is null, we write null into the column, rather than an empty array. When this happens, JSONB_ARRAY_ELEMENTS fails. We could, instead, choose to write an empty array in such cases. This would make policy queries simpler, as we wouldn't need to have a switch case on null instances. I'm not sure whether we should do this from a philosophical standpoint though.

See this ticket for additional context: #3565 (comment)

[disq]

I think they should be valid json object. But can we tell if they're supposed to be arrays or objects? We could do NOT NULL DEFAULT '[]' on the dest side and accomodate/skip on insertion but then would it fail if we're expecting {} instead (on some cases)?

[yevgenypats]

If we want to have defaults then we either need to take care of that on the resolver side or have some kind of default on sdk side, not on destination as it's not db agnostic and will create infinite amount of work.

but seems to me that we know where are those special columns where we dont want null (so it's not really default) but we want to have a default if not null so maybe we can just do that on the resolver side with PathNotNullResolver?

[disq]

PathNotNullResolver could just look at the cq type, but since cq types don't have a default empty value... maybe call it PathDefaultResolver and give it a default in the call... as in func PathDefaultResolver(path string, default interface{}) ColumnResolver and if the value is null, have the given default (assumably casted to the ValueType, but not necessary here) and set into resource? (but then do JSON types get [] or {} as defaults? or have two distinct types? etc)

In addition, this doesn't fix complex queries where the requested value is multiple levels deep in an object... In PG:

SELECT column->'json_field'->'nested_value' FROM table

will still return null and not an empty array, even if we set column to be {}.

[shimonp21]

Closing for now after cloudquery/plugin-sdk#430.
Let's reopen to discuss again if we run into issues related to nested fields (which cloudquery/plugin-sdk#430 doesn't fix).