Integration Architect (Presenter)
ksqlDB can work with complex types, including STRUCT and ARRAY, as well as primitive types.
Since nested JSON can be difficult to work with, ksqlDB makes it easy to transform the schema of an event stream. You can convert nested fields into top-level fields and leave the nested field out of the SELECT for a new stream. The original stream is not changed, so downstream operations that are expecting the original data structure are not affected.
To access a nested field in ksqlDB, use ->, like so:
ksql> SELECT ADDRESS->STREET FROM ORDERS
EMIT CHANGES LIMIT 1;
+-------------------+
|STREET |
+-------------------+
|52994 Debra Plaza |
Using this approach, you can create a flattened version of a source stream that contains nested fields:
CREATE STREAM ORDERS_FLAT AS
SELECT TIMESTAMPTOSTRING(ORDERTIME, 'yyyy-MM-dd HH:mm:ss') AS ORDER_TIMESTAMP,
ORDERID,
ITEMID,
ORDERUNITS,
ADDRESS->STREET AS ADDRESS_STREET,
ADDRESS->CITY AS ADDRESS_CITY,
ADDRESS->STATE AS ADDRESS_STATE
FROM ORDERS;
We will only share developer content and updates, including notifications when new content is added. We will never send you sales emails. 🙂 By subscribing, you understand we will process your personal information in accordance with our Privacy Statement.
Hi, I'm Allison Walther with Confluent. Nested JSON can be difficult to work with. KsqlDB offers a simple way to fix that. KsqlDB makes it easy to transform the schema of an event stream. For example, if we have events with a nested record structure and we want to flatten them. Using the as operator, we can convert the nested fields into top-level fields and leave the nested field out of the select for our new stream. The new stream contains all of the events of the original, but with our new schema. It's important to note that the original stream has not changed, so downstream applications that are expecting the nested data structure are not affected. That's it for this lesson. Let's move on to an exercise.