JSONB_POPULATE_RECORD cannot convert jsonb strings to number type

[fuyufjh]

Describe the bug

JSONB_POPULATE_RECORD & JSONB_POPULATE_RECORDSET cannot convert jsonb strings to any SQL number type

Besides, parsing orders of magnitudes slower when schema set at source level versus reading in the raw jsonb and using a function like JSONB_POPULATE_RECORD in an MV (slack archive)

Error message/log

No response

To Reproduce

No response

Expected behavior

Better to try_parse the string instead of reporting error and leaving a null.

PostgresQL:

test=# CREATE TYPE test_table_type AS (id BIGINT);
CREATE TYPE
test=# select JSONB_POPULATE_RECORD(NULL::test_table_type, '{"id": "12345"}'::jsonb);
 jsonb_populate_record
-----------------------
 (12345)
(1 row)

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

No response

[fuyufjh]

Analysis:

populate_struct calls JsonbRef::to_datum(ty) to convert JSON value to datum.

risingwave/src/common/src/types/jsonb.rs

Line 523 in 119e17e

_ => Self(v).to_datum(ty)?,

For numbers, it only calls jsonbb::ValueRef::as_number(), thus string values will cause an error.

risingwave/src/common/src/types/jsonb.rs

Lines 432 to 436 in 119e17e

	DataType::Int16 => ScalarImpl::Int16(self.as_number()?.try_into()?),
	DataType::Int32 => ScalarImpl::Int32(self.as_number()?.try_into()?),
	DataType::Int64 => ScalarImpl::Int64(self.as_number()?.try_into()?),
	DataType::Float32 => ScalarImpl::Float32(self.as_number()?.try_into()?),
	DataType::Float64 => ScalarImpl::Float64(self.as_number()?),

Need to modify here to parse string values as well.