Search code examples
sqlapache-kafkaksqldb

How to filter nested data with EXTRACTJSONFIELD

The PAYLOAD column stores a Json stringified value such as:

"{\"key\":{\"subkey\":\"subvalue\"}}"

Running the ksql statement

SELECT EXTRACTJSONFIELD(PAYLOAD, '$.key') AS key FROM my_stream WHERE (key => subkey = 'subvalue');

results to an error. Is there a way to filter so it returns only if subkey is equal to "subvalue" ?

Solution

  • To get a value from a nested key stored as a json string we specify a complete key's path as $.key.subkey:

    SELECT EXTRACTJSONFIELD(PAYLOAD, '$.key.subkey') AS data_payload FROM my_stream;