CODEWITHSUNDEEP
apache-kafkaconfluent-platformksqldb
Florentin Hennecker
Florentin Hennecker

Reputation: 2174

KSQL Join streams with condition on struct field

I have two streams defined each from a topic on which JSON messages are published a bit like this:

{"payload": {"some_id": "123"}}

Their corresponding streams are defined like this:

CREATE STREAM mystream
  (payload STRUCT <someid varchar>)
WITH (kafka_topic='mytopic', value_format='JSON')

When I try to JOIN the two streams together:

SELECT
  s.payload->some_id,
  o.payload->other_id
FROM mystream s
LEFT JOIN otherstream o ON s.payload->some_id = o.payload->other_id;

I get the following error:

Invalid comparison expression 'S.PAYLOAD->SOME_ID' 
in join '(S.PAYLOAD->SOME_ID = O.PAYLOAD->OTHER_ID)'.
Joins must only contain a field comparison.

Is it not possible to join two streams based on a struct field? Do I first need to publish a stream that flattens each source stream before I can perform the JOIN?

Upvotes: 0

Views: 1067

Answers (1)

Robin Moffatt
Robin Moffatt

Reputation: 32100

Correct, this is not currently possible. Feel free to upvote the issue tracking it here: https://github.com/confluentinc/ksql/issues/4051

As you say, the workaround is to flatten it in another stream first and then join it.

Upvotes: 1

Related Questions