Support defining schema in STRUCT type for mongodb cdc

[hzxa21]

Currently data ingested from mongodb cdc is parsed as the JSONB type. See example here.

Recently we saw user requesting supporting strongly typed schema for data ingested by mongodb cdc, which leads to supporting defining strongly typed STRUCT in CREATE TABLE statement for mongodb cdc. Exmaple:

CREATE TABLE mongdb_source_table (
    payload STRUCT<
        foo STRUCT<
            foo1 JSONB,
            foo2 BIGINT
        >,
        bar STRUCT<
            bar1 STRUCT<
                bar1_1 VARCHAR,
                bar1_2 VARCHAR,
                bar1_3 STRUCT<
                    bar1_3_array STRUCT<
                        item1 DOUBLE,
                        item2 BIGINT,
                        ...
                    >[]
                >,
         ...
) WITH (
  connector=mongodb-cdc
  ...
);

We already support parsing jsonb to struct via josnb_populate_record so we can borrow the implementation here as well. The expected behavior is:

• If the fields of a document ingested from mongodb is exactly the same as the defined struct, the document will be parsed and ingested successfully.
• If the fields of a document ingested from mongodb is a superset of the defined struct, the document will be parsed and ingested successfully by ignoring the extra fields.
• If the fields of a document ingested from mongodb is a subset of the defined struct, the document will be parsed and ingested successfully by filling in NULL for the missing fields.
• If the payload a given field in the document ingested from mongodb cannot be converted to the type defined in the struct (e.g. cannot cast text to int), the whole document will be skipped by filling in NULL for the struct.

[xiangjinwu]

The conversion rule may be different from jsonb_populate_record. Or in other words, AVOID updating jsonb_populate_record behavior to be closer to their usage in MongoDB. The function is supposed to be PostgreSQL compatible.

It can be a dialect here instead:

risingwave/src/connector/src/parser/unified/json.rs

Lines 154 to 155 in 17c4cd2

	impl JsonParseOptions {
	pub const CANAL: JsonParseOptions = JsonParseOptions {

[ClSlaid]

Do we really need the payload field? I prefer to:

• users just need to set the inner fields.
• turn on a strong_schema option in connector configurations.