Search code examples
apache-kafkaconfluent-platformksqldb

KSQL EXTRACTJSONFIELD space in key name

I'm having a problem with spaces in names when using EXTRACTJSONFIELD in KSQL For example: 

SELECT EXTRACTJSONFIELD(payload, '$.Successful Login') FROM MYSTREAM;

--this always returns NULL

How should I handle spaces in key names?

I have tried:

EXTRACTJSONFIELD(payload, '$.Successful%Login')
EXTRACTJSONFIELD(payload, '$.Successful%20Login')
EXTRACTJSONFIELD(payload, '$.[Successful Login]')
EXTRACTJSONFIELD(payload, '$."Successful Login"')
Solution

  • tl;dr: Use [\\" label \\"] to reference the field

    Test data: 

    echo '{"id":1,"test":{"Successful Login":1}}' | kafkacat -b localhost:9092 -t test1

    Declare stream in KSQL and check message: 

    ksql> create stream test1 (id bigint, test varchar) with (kafka_topic='test1', value_format='json');

 Message
----------------
 Stream created
----------------
ksql> select id, test from test1;
1 | {"Successful Login":1}

    Un-escaped reference of column doesn't work: 

    ksql> select extractjsonfield(test,'$.Successful Login') from test1;
null

    Escape column using [\\" (also observe not using dot-notation either): 

    ksql> select extractjsonfield(test,'$[\\"Successful Login\\"]') from test1;
1

    This solution is courtesy of novikovantonio on this issue