Search code examples
apache-kafkaksqldb

kafka ksql extract json fleld literal dollar symbol

I've got a data stream coming from the mongo CDC connector, but the trouble is that the stream key is in the form of a JSON string.

e.g. {"id":"{ \"$oid\" : \"5bbb0c70cd0b9c06cf06c9c1\"}"}

I know that I can use extractjsonfield method to extract the data using jsonpath, however, I can't figure out how to extract the literal dollar symbol I've tried: $.id.$oid $.id[\$oid] $.id.*

each time I get a null response, any ideas?

Solution

  • I guess that your problem is related to issue #1403.
    You can use [\\" field_name \\"] to reference the column. For example,

    SELECT EXTRACTJSONFIELD(test,'$[\\"$oid\\"]') FROM testing;