Get Started Free
‹ Back to courses
course: ksqlDB 101

Flatten Nested Records with ksqlDB

1 min
Allison

Allison Walther

Integration Architect (Presenter)

Flatten Nested Records with ksqlDB

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;

Use the promo code KSQLDB101 & CONFLUENTDEV1 to get $25 of free Confluent Cloud usage and skip credit card entry.

Be the first to get updates and new content

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.

Flatten Nested Records with ksqlDB

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.