How to extract nested JSON object from kafka in flink table?

Question

Here is a json format message in kafka.

{
    "@timestamp": "2021-08-14T11:11:00.301Z",
    "@metadata": {
        "beat": "filebeat",
        "type": "_doc",
        "version": "7.14.0"
    },
    "message": {
        "k1": "v1",
        "k2": "v2"
    }
}

Then, i use flink talbe to consume data from kafka.

CREATE TABLE table_1
(
    `message` ROW(k1 STRING, k2 STRING)
)
WITH (
    'connector' = 'kafka',
    'topic' = 'topic1',
    'json.ignore-parse-errors' = 'true',
    'properties.bootstrap.servers' = '127.0.0.1:9092',
    'properties.group.id' = 'groupid1',
    'scan.startup.mode' = 'earliest-offset',
    'format' = 'json'
);

select message.k1, message.k2 from table_v1 LIMIT 10;

The source message is produced by filebeat(output.kafka).Is it posiable to extract the value of message directly in flink table? Such as:

CREATE TABLE table_2
(
    `k1` STRING,
    `k2` STRING
)
WITH (
    'connector' = 'kafka',
    'topic' = 'topic1',
    'json.ignore-parse-errors' = 'true',
    'properties.bootstrap.servers' = '127.0.0.1:9092',
    'properties.group.id' = 'groupid1',
    'scan.startup.mode' = 'earliest-offset',
    'format' = 'json'
);

select k1, k2 from table_v2 LIMIT 10;

`table_1` is working? I don't see anything wrong with `select message.k1, message.k2` — OneCricketeer, Jun 27 '22 at 18:43

score 0 · Answer 1 · answered Jun 27 '22 at 18:45

If you want this

CREATE TABLE table_2
(
    `k1` STRING,
    `k2` STRING
)
WITH (
    'connector' = 'kafka',

The input topic data should look like this

{"k1": "v1", "k2": "v2"}

i.e. you need to "flatten" the records that filebeat creates

Or you might be able to do CREATE TABLE table_2 AS SELECT message.k1, message.k2 FROM table_v1

How to extract nested JSON object from kafka in flink table?

1 Answers1