Course: ksqlDB 101

Flatten Nested Records with ksqlDB

1 min
Allison WaltherIntegration Architect (Course Presenter)
Robin MoffattStaff Developer Advocate (Course Author)

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 to get $101 of free Confluent Cloud usage

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.