Stuck with this, every where I go, different ideas... data is a full string payload, as per attached.
the data.account field have an array of accounts Flink 2.2 seem to be getting some new functionality from_json. but well, it's not in 1.20 and 2.0 is not allowing me to CDC consume from a postgres table, even when using the newest 3.4 library. I've had this below error can't cast now on several attempts.... ==> Cast function cannot convert value of type VARCHAR(2147483647) to type VARCHAR(2147483647) ARRAY Been stuck with this one for a while, hope someone can help. Example payload attached. Flink SQL> select > JSON_VALUE(data, '$.nationalid') AS nationalid > ,JSON_VALUE(data, '$._id') AS _id -- UUID generated by app, inside 'data' / json payload > ,JSON_VALUE(data, '$.name') AS name > ,JSON_VALUE(data, '$.surname') AS surname > ,JSON_VALUE(data, '$.gender') AS gender > ,JSON_VALUE(data, '$.dob') AS dob > ,JSON_VALUE(data, '$.marital_status') AS marital_status > ,JSON_VALUE(data, '$.status') AS status > ,JSON_QUERY(data, '$.address') AS address > ,CAST( > JSON_QUERY(data, '$.account') AS > ARRAY<ROW< > fspiagentaccountid STRING > ,accountid STRING > ,fspiid STRING > ,fspiagentid STRING > ,accounttype STRING > ,membername STRING > ,cardholder STRING > ,cardnumber STRING > ,expdate STRING > ,cardnetwork STRING > ,issuingbank STRING > >> > ) AS account > ,created_at AS created_at > FROM postgres_catalog.inbound.adults; [ERROR] Could not execute SQL statement. Reason: org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type VARCHAR(2147483647) to type RecordType(VARCHAR(2147483647) fspiagentaccountid, VARCHAR(2147483647) accountid, VARCHAR(2147483647) fspiid, VARCHAR(2147483647) fspiagentid, VARCHAR(2147483647) accounttype, VARCHAR(2147483647) membername, VARCHAR(2147483647) cardholder, VARCHAR(2147483647) cardnumber, VARCHAR(2147483647) expdate, VARCHAR(2147483647) cardnetwork, VARCHAR(2147483647) issuingbank) ARRAY Flink SQL> WITH unnested_accounts AS ( > SELECT > JSON_VALUE(account_item, '$.fspiAgentAccountId') AS fspiagentaccountid > ,JSON_VALUE(account_item, '$.accountId') AS accountid > ,JSON_VALUE(account_item, '$.fspiId') AS fspiid > ,JSON_VALUE(account_item, '$.fspiAgentId') AS fspiagentid > ,JSON_VALUE(account_item, '$.accountType') AS accounttype > ,JSON_VALUE(account_item, '$.memberName') AS membername > ,JSON_VALUE(account_item, '$.cardHolder') AS cardholder > ,JSON_VALUE(account_item, '$.cardNumber') AS cardnumber > ,JSON_VALUE(account_item, '$.expDate') AS expdate > ,JSON_VALUE(account_item, '$.cardNetwork') AS cardnetwork > ,JSON_VALUE(account_item, '$.issuingBank') AS issuingbank > FROM postgres_catalog.inbound.adults > CROSS JOIN UNNEST( > CAST(JSON_QUERY(data, '$.account') AS ARRAY<STRING>) > ) AS t(account_item) > ) > SELECT > ARRAY_AGG( > ROW( > fspiagentaccountid > ,accountid > ,fspiid > ,fspiagentid > ,accounttype > ,membername > ,cardhol der > ,cardnumber > ,expdate > ,cardnetwork > ,issuingbank > ) > ) AS accounts > FROM unnested_accounts; [ERROR] Could not execute SQL statement. Reason: org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type VARCHAR(2147483647) to type VARCHAR(2147483647) ARRAY -- You have the obligation to inform one honestly of the risk, and as a person you are committed to educate yourself to the total risk in any activity! Once informed & totally aware of the risk, every fool has the right to kill or injure themselves as they see fit!
adults.json
Description: application/json
