George Leonard
09/10/2025, 4:46 AMFlink SQL> create table c_paimon.outbound.adultsxx WITH (
> 'file.format' = 'parquet'
> ,'compaction.min.file-num' = '2'
> ,'compaction.early-max.file-num' = '50'
> ,'snapshot.time-retained' = '1h'
> ,'snapshot.num-retained.min' = '5'
> ,'snapshot.num-retained.max' = '20'
> ,'table.exec.sink.upsert-materialize'= 'NONE'
> ) AS
> select
> JSON_VALUE(data, '$.nationalid') AS nationalid
> ,JSON_VALUE(data, '$._id') AS _id
> ,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
> ,CAST(JSON_QUERY(data, '$.account') AS ARRAY<STRING>) as account
> ,JSON_QUERY(data, '$.address') AS address
> ,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 VARCHAR(2147483647) ARRAYEvan NotEvan
09/10/2025, 7:10 AMCREATE TABLE c_paimon.outbound.adultsxx WITH (
'file.format' = 'parquet',
'compaction.min.file-num' = '2',
'compaction.early-max.file-num' = '50',
'snapshot.time-retained' = '1h',
'snapshot.num-retained.min' = '5',
'snapshot.num-retained.max' = '20',
'table.exec.sink.upsert-materialize'= 'NONE'
) AS
SELECT
JSON_VALUE(data, '$.nationalid') AS nationalid,
JSON_VALUE(data, '$._id') AS _id,
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,
from_json(JSON_QUERY(data, '$.account'), 'ARRAY<MAP<STRING, STRING>>') as account,
JSON_QUERY(data, '$.address') AS address,
created_at AS created_at
FROM postgres_catalog.inbound.adults;George Leonard
09/10/2025, 7:12 AMGeorge Leonard
09/10/2025, 7:17 AMFlink SQL> CREATE TABLE c_paimon.outbound.adultsxx WITH (
> 'file.format' = 'parquet',
> 'compaction.min.file-num' = '2',
> 'compaction.early-max.file-num' = '50',
> 'snapshot.time-retained' = '1h',
> 'snapshot.num-retained.min' = '5',
> 'snapshot.num-retained.max' = '20',
> 'table.exec.sink.upsert-materialize'= 'NONE'
> ) AS
> SELECT
> JSON_VALUE(data, '$.nationalid') AS nationalid,
> JSON_VALUE(data, '$._id') AS _id,
> 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,
> from_json(JSON_QUERY(data, '$.account'), 'ARRAY<MAP<STRING, STRING>>') as account,
> JSON_QUERY(data, '$.address') AS address,
> created_at AS created_at
> FROM postgres_catalog.inbound.adults;
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature from_json(<CHARACTER>, <CHARACTER>)George Leonard
09/10/2025, 7:22 AMGeorge Leonard
09/10/2025, 7:31 AMGeorge Leonard
09/10/2025, 7:37 AMGeorge Leonard
09/10/2025, 8:04 AMCREATE TABLE my_source_table (
id INT,
json_data STRING
) WITH (
'connector' = 'datagen',
'rows-per-second' = '1',
'fields.id.kind' = 'sequence',
'fields.id.start' = '1',
'fields.id.end' = '10',
'fields.json_data.kind' = 'sequence',
'fields.json_data.start' = '{"name": "Alice", "age": 30}',
'fields.json_data.end' = '{"name": "Bob", "age": 25}'
);
CREATE TABLE my_parsed_table (
id INT,
user_name STRING,
user_age INT
) WITH (
'connector' = 'print'
);
INSERT INTO my_parsed_table
SELECT
id,
parsed_json.name,
parsed_json.age
FROM
my_source_table,
LATERAL TABLE(FROM_JSON(json_data, 'ROW<name STRING, age INT>')) AS parsed_json;
I refactored this into
SELECT
parsed_json.fspiagentaccountid
,parsed_json.accountid
,parsed_json.fspiid
,parsed_json.fspiagentid
,parsed_json.accounttype
,parsed_json.membername
FROM
postgres_catalog.inbound.adults,
LATERAL TABLE(FROM_JSON(JSON_QUERY(data, '$.account'), '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 parsed_json;
and still getting
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature FROM_JSON(<CHARACTER>, <CHARACTER>)George Leonard
09/10/2025, 8:17 AMFlink SQL> CREATE TABLE my_source_table (
> id INT,
> json_data STRING
> ) WITH (
> 'connector' = 'datagen',
> 'rows-per-second' = '1',
> 'fields.id.kind' = 'sequence',
> 'fields.id.start' = '1',
> 'fields.id.end' = '10',
> 'fields.json_data.kind' = 'sequence',
> 'fields.json_data.start' = '{"name": "Alice", "age": 30}',
> 'fields.json_data.end' = '{"name": "Bob", "age": 25}'
> );
[INFO] Execute statement succeeded.
Flink SQL> CREATE TABLE my_parsed_table (
> id INT,
> user_name STRING,
> user_age INT
> ) WITH (
> 'connector' = 'print'
> );
[INFO] Execute statement succeeded.
Flink SQL> INSERT INTO my_parsed_table
> SELECT
> id,
> parsed_json.name,
> parsed_json.age
> FROM
> my_source_table,
> LATERAL TABLE(FROM_JSON(json_data, 'ROW<name STRING, age INT>')) AS parsed_json;
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature FROM_JSON(<CHARACTER>, <CHARACTER>)George Leonard
09/11/2025, 4:49 PM