Controlling string unescaping in Clickhouse?

user_2327670 0
Viewed 1127

I am trying to load JSON data from Kafka into Clickhouse where the JSON contains some escape sequences. E.g:

:) SELECT JSONExtractRaw('{"message": "Hello \"to\" you!"}', 'message')

SELECT JSONExtractRaw('{"message": "Hello "to" you!"}', 'message')

┌─JSONExtractRaw('{"message": "Hello "to" you!"}', 'message')─┐
│ "Hello "                                                    │
└─────────────────────────────────────────────────────────────┘

It appears that prior to calling JSONExtractRaw, the input strings are unescaped, which produces invalid JSON. The unescaping seems to be reproducible with this minimal example:

:) SELECT 'Hello \"there\"'

SELECT 'Hello "there"'

┌─'Hello "there"'─┐
│ Hello "there"   │
└─────────────────┘

I am wondering if it's possible to retain the original (escaped) representation of the input.

Thank you!

clickhouse
2 Answers
SELECT '{"message": "Hello \\"to\\" you!"}'

┌─'{"message": "Hello \\"to\\" you!"}'─┐
│ {"message": "Hello \"to\" you!"}     │
└──────────────────────────────────────┘
user_116443080

Let's test your case without CLI to emulate the table filled by data coming outside:

echo '{"message": "Hello \"to\" you!"}' | clickhouse-client \
    --password 12345 \
    --query="INSERT INTO test.json_001 SELECT json FROM input('json String') FORMAT CSV"

create database test;    
create table test.json_001 (json String) Engine=Memory;

Get required data:

clickhouse-client \
    --password 12345 \
    --query="SELECT JSONExtractRaw(json, 'message'), JSONExtractString(json, 'message') FROM test.json_001"

# result:
# "Hello \\"to\\" you!"   Hello "to" you!

SELECT
    JSONExtractRaw(json, 'message'),
    JSONExtractString(json, 'message')
FROM test.json_001

/* result
┌─JSONExtractRaw(json, 'message')─┬─JSONExtractString(json, 'message')─┐
│ "Hello \"to\" you!"             │ Hello "to" you!                    │
└─────────────────────────────────┴────────────────────────────────────┘
*/

It works as it should. I don't see any problems.

user_3032980
Related