Flatten Nested Records with ksqlDB

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;