... can anyone see anything wrong with this... ``...
# troubleshooting
g
... can anyone see anything wrong with this...
Copy code
it'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
Copy code
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
Copy code
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'
);