How to create a nested json schema with Stream

Question

The INPUT_STREAM in Kafka was created with ksql statement below:

CREATE STREAM INPUT_STREAM (year STRUCT<month STRUCT<day STRUCT<hour INTEGER, minute INTEGER>>>) WITH (KAFKA_TOPIC = 'INPUT_TOPIC', VALUE_FORMAT = 'JSON');

It defines four levels nested json schema with fields year, month and day and hour and minute, like the one below:

{
  "year": {
    "month": {
      "day": {
        "hour": string,
        "minute": string
        }
      }
    }
}

I want to create a second OUTPUT_STREAM that will read the messages from INPUT_STREAM and re-map its field names to some custom ones. I want to grab the hour and minute values and place them in a nested json below the fields one and two, like this one below:

{
  "one": {
    "two": {
      "hour": string,
      "minute": string
      }
    }
}

I go ahead and put together ksql statement to create OUTPUT_STREAM

CREATE STREAM OUTPUT_STREAM WITH (KAFKA_TOPIC='OUTPUT_TOPIC', REPLICAS=3) AS SELECT YEAR->MONTH->DAY->HOUR ONE->TWO->HOUR FROM INPUT_STREAM EMIT CHANGES;

The statement fails with an error. Is there a syntax error in this statement? Is it be possible to specify the destination field name like I do here with

...AS SELECT YEAR->MONTH->DAY->HOUR ONE->TWO->HOUR FROM... ?

I've tried to use STRUCT instead of ONE->TWO->HOUR:

CREATE STREAM OUTPUT_STREAM WITH (KAFKA_TOPIC='OUTPUT_TOPIC', REPLICAS=3) AS SELECT YEAR->MONTH->DAY->HOUR ONE STRUCT<TWO STRUCT<HOUR VARCHAR>> FROM INPUT_STREAM EMIT CHANGES;

It errors out too and doesn't work

score 0 · Answer 1 · answered Oct 04 '22 at 13:29

To create structure in ksqlDB

SELECT
  STRUCT(
    COLUMN_NEW_NAME_1 := OLD_COLUMN_NAME_1,
    COLUMN_NEW_NAME_2 := OLD_COLUMN_NAME_2
  ) as STRUCT_COLUMN_NAME
FROM OLD_TABLE_OR_STREAM

So answer to your question would be, yes you are having syntax error.

SELECT 
  STRUCT(
    TWO := STRUCT(
      HOUR := YEAR->MONTH->DAY->HOUR,
      MINUTE := YEAR->MONTH->DAY->MINUTE
    )
  ) as ONE
FROM INPUT_STREAM;

How to create a nested json schema with Stream

1 Answers1