3

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"')
Matthias J. Sax
  • 59,682
  • 7
  • 117
  • 137
Jaco O
  • 35
  • 4

2 Answers2

2

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

Robin Moffatt
  • 30,382
  • 3
  • 65
  • 92
2

I just tried Robin's response using KSQL 6 (CLI v6.0.0, Server v6.0.0). For me it does not work using two backslashes. For me it only works using one backslash or no backslash in the ksqldb cli like so:

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

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

For me it only works without backslashes when starting the query from the confluent command center (version 6.0.0) as well as when handing the query to a headless ksqldb server (confluentinc/cp-ksqldb-server:6.0.0 image) using a query file.

Michamei
  • 348
  • 1
  • 11