Company or project name

Gridcog

Use case

As per #84803 , we also have a big JSON column where we want to add a type hint, but our data doesn't perfectly conform to the type hint so we're prevented from doing so. We'd be happy to just default/null the non-conformant field.

In the linked issue, @Avogar mentioned this setting would be worth adding, I'm opening this issue to re-request / track (sorry :) )

Describe the solution you'd like

Setting json_type_use_default_on_incompatible_cast - if enabled and a cast to JSON fails, the failing nested path should be set to a default. When dealing with nested fields, the default-setting should happen at the failing field level, leaving the rest of the structure intact instead of skipping the whole thing.

Examples:

'{"x": "asdf", "y": "asdf"}'::JSON(
  x Int32
)
-- = {x: 0, y: asdf}

'{"records": [{"x": "asdf", "y": "asdf"}, {"x": 123}]}'::JSON(
  records Array(JSON(x Int32))
)
-- = {records: [{x: 0, y: asdf}, {x: 123}]}

'{"kvs": {"a": [{"x": "asdf", "y": "asdf"}, {"x": 123}], "b": [{"x": 456}], "c": {"x":345}}}'::JSON(
  kvs Map(String, Array(JSON(x Int32)))
)
-- = {kvs: Map("a" => [{x: 0, y: asdf}, {x: 123}], "b" => [{x: 456}], "c": []}

Describe alternatives you've considered

Any other mechanism to allow zeroing/nulling the failing field would be fine too, but we kind of need something at least.

Additional context

No response