Extract value from clickhouse string data type which is a json object?

Question

I have a table column of type string, but it's value is a json object, like {"is_true":true},but not '{"is_true":true}'.

I want to extract the value of "is_true".

SELECT JSONExtract(column_name,'$.is_true') FROM database_name.table_name;

But i got this error: Syntax error (data type): failed at position 1 ('$'): $.is_true. Expected one of: data type, nested table, identifier: While processing open_access, JSONExtract(column_name, '$.is_true'). (SYNTAX_ERROR)

Then i try to use: SELECT JSONExtractBool(column_name,'$.is_true') FROM database_name.table_name; but i got 0, which should be 1.

Can anyone help me? I know that '{"is_true":true}' should be work. but i have too many rows.

Answer 1

I don't know which clickhouse version you use. From the official docuemntation, JSONExtract does not require the $ to work, maybe that's the problem。

I am on ClickHouse 22.6.1.823 and i can extract is_true value from the following sql。

SELECT 
'{"is_true":true}' as jobj,
toTypeName(jobj),
-- the result is 1
JSONExtractBool(jobj, 'is_true') as result;

Answer 2

with '{"is_true":true}' as json_str select JSONExtract(json_str,'is_true','Bool');