How to Query Nested JSON in ksqlDB

If you haven’t got your data as a stream yet, first create a stream that parses the JSON topic.

You can query nested fields as seen below:

SELECT 
  score, 
  user, 
  user->name, 
  user->age 
FROM scores_stream;

+------+-----------------------+--------+----+
|SCORE |USER                   |NAME    |AGE |
+------+-----------------------+--------+----+
|4.9   |{NAME=Charlie, AGE=54} |Charlie |54  |
|4.5   |{NAME=Bob, AGE=25}     |Bob     |25  |
|3.8   |{NAME=Alice, AGE=30}   |Alice   |30  |
+------+-----------------------+--------+----+

Given a stream created like this:

CREATE STREAM scores_stream (
  score DOUBLE,
  user STRUCT<name STRING, age INT>
) WITH (
  KAFKA_TOPIC = 'scores_topic',
  VALUE_FORMAT = 'JSON'
);

Was this article helpful?