Hi all, We are working with an `upsert-kafka` tab...
# troubleshooting
j
Hi all, We are working with an
upsert-kafka
table whose message value is split into two nested json sections. Similar to this example:
Copy code
{
  "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:
Copy code
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:
Copy code
SQL parse failed. Encountered "." at line X. Was expecting one of: ")", ",".
If you alias the nested field such as:
Copy code
main.order_id AS order_id
PRIMARY KEY (order_id) NOT ENFORCED
Flink will throw the error:
Copy code
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!