Flink SQL 处理json对象数组,列变多行

数据格式:

{
    "s_time": "2022-05-11 11:01:49.254",
    "que_id": "5a8fb527-82a7-4e33-91a1-55ec86cacbe4",
    "count": 2,
    "data": [
      {
        "code": "dd",
        "dealtime": "2022-05-11 22:01:49.240"
      },
      {
        "code": "dd",
        "dealtime": "2022-05-11 22:01:49.240"
      }
}

sql:

--连接kafka
create table kafkastream ( 
    `source_time` string,
    queue_id string,
    `count` bigint,
    `data` ARRAY<ROW<code string, dealtime string>>,
    proctime as PROCTIME(),
    ts TIMESTAMP(3),
    WATERMARK FOR ts AS ts - INTERVAL '2' SECOND
)
with ( 
    'connector' = 'kafka',
    'topic' = 'test_3',
    'properties.group.id' = 'test3',
    'scan.startup.mode' = 'latest-offset',
    'format' = 'json',
    'json.fail-on-missing-field' = 'false'
);
--处理
select
     code,
     DATE_FORMAT(dealtime, 'yyyyMMdd') as date
     proctime,
     ts
from
     kafkastream CROSS JOIN UNNEST(`data`) AS t (code,dealtime)