Jeff McFarlane
04/24/2025, 8:51 PMupsert-kafka table whose message value is split into two nested json sections. Similar to this example:
{
"header": {
"event_time": 1745499600000,
"source": "web-shop"
},
"main": {
"order_id": "o456",
"user_id": "u123",
"amount": 100.5
}
}
Is it possible to define a primary key for the upsert-kafka connector when your key is in nested json? The only way I have been able to get it to work is by "pre-flattening" the json so the key fields are top-level. Is that an actual requirement?
In this example we might do something like:
CREATE TABLE orders (
header ROW<event_time BIGINT, source STRING>,
main ROW<user_id STRING, order_id STRING, amount DECIMAL(18, 4)>,
PRIMARY KEY (main.order_id) NOT ENFORCED
) WITH (
'connector' = 'upsert-kafka',
'topic' = 'orders',
'key.format' = 'json',
'value.format' = 'json'
);
Flink will complain:
SQL parse failed. Encountered "." at line X. Was expecting one of: ")", ",".
If you alias the nested field such as:
main.order_id AS order_id
PRIMARY KEY (order_id) NOT ENFORCED
Flink will throw the error:
A PRIMARY KEY constraint must be declared on physical columns.
Has anyone found a way to reference a nested field in the PK without flattening? (or can we use the values in the message's key) Thanks!