George Leonard
09/09/2025, 2:23 PMit's failing on 'CAST(JSON_QUERY(data, '$.account') AS ARRAY<STRING>)
trying to get the array of accounts into a json object,
More than happy to pre create the table as a complex table if need be and then do a insert into select... actually prefer that pattern, just running out of ideas how to get this done. and my eyes are seeing double from reading docs.
-- postgres_catalog.inbound.adultsx -> Target table
create table c_paimon.outbound.adultsx 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
,JSON_QUERY(data, '$.address') AS address
,CAST(JSON_QUERY(data, '$.account') AS ARRAY<STRING>) AS account
,created_at AS created_at
FROM postgres_catalog.inbound.adults;
I'm consuming the data see atached, from Postgres using CDC into the below table.
-- postgres_catalog.inbound.adults -> CDC Source
CREATE OR REPLACE TABLE postgres_catalog.inbound.adults (
id BIGINT
,nationalid VARCHAR(14) --NOT NULL
,data STRING -- The json as per attached sit in here...
,created_at TIMESTAMP_LTZ(3)
,WATERMARK FOR created_at AS created_at - INTERVAL '15' SECOND
,PRIMARY KEY (nationalid) NOT ENFORCED
) WITH (
'connector' = 'postgres-cdc'
,'hostname' = 'postgrescdc'
,'port' = '5432' -- NOTE: this is the port of the db on the container, not the external docker exported port via a port mapping.
,'username' = 'dbadmin'
,'password' = 'dbpassword'
,'database-name' = 'demog'
,'schema-name' = 'public'
,'table-name' = 'adults'
,'slot.name' = 'adults0'
,'scan.incremental.snapshot.enabled' = 'true' -- experimental feature: incremental snapshot (default off)
,'scan.startup.mode' = 'initial' -- <https://nightlies.apache.org/flink/flink-cdc-docs-release-3.1/docs/connectors/flink-sources/postgres-cdc/#startup-reading-position>
,'decoding.plugin.name' = 'pgoutput'
);