2

One of my topics has a string-json as a key - {"city":"X","id":22}. In my ksql statement I want to extract it to 2 different fields not one so I can later filter and join. In the docs it seems to allow me to stick only the whole string into the key instead of allowing to format it as JSON (just like FORMAT_VALUE) see below... Any suggestions for an elegant solution to this?

VALUE_FORMAT (required) Specifies the serialization format of the message value in the topic. Supported formats: JSON, DELIMITED, and AVRO

KEY Associates the message key in the Kafka topic with a column in the KSQL stream.

Matthias J. Sax
  • 59,682
  • 7
  • 117
  • 137
andrew shved
  • 137
  • 1
  • 8

1 Answers1

6

You should be able to do this using the EXTRACTJSONFIELD UDF on the ROWKEY column. The ROWKEY column is a "column" in your schema that contains the key for a given row. The EXTRACTJSONFIELD UDF lets you extract fields from columns that contain json strings. So, for your example you could run:

SELECT EXTRACTJSONFIELD(ROWKEY, '$.city') AS city, EXTRACTJSONFIELD(ROWKEY, '$.id') AS id FROM FOO;
Rohan D.
  • 136
  • 2