Clickhouse convert table with json content each row into table

Question

I have tried to transform json in rows into a table with this json fields. After looking at Clickhouse documentation I cound't find some clickhouse FUNCTION that can handle this task

Here is the table with the

col_a
{"casa":2,"value":4}
{"casa":6,"value":47}

The proposal is to transform using only Clickhouse SQL (CREATE WITH SELECT) int this table

casa	value
2	4
6	47

Answer 1

SELECT
    '{"casa":2,"value":4}' AS j,
    JSONExtractKeysAndValuesRaw(j) AS t
┌─j────────────────────┬─t────────────────────────────┐
│ {"casa":2,"value":4} │ [('casa','2'),('value','4')] │
└──────────────────────┴──────────────────────────────┘

SELECT
    '{"casa":2,"value":4}' AS j,
    JSONExtract(j, 'Tuple(casa Int64, value Int64)') AS t,
    tupleElement(t, 'casa') AS casa,
    tupleElement(t, 'value') AS value
┌─j────────────────────┬─t─────┬─casa─┬─value─┐
│ {"casa":2,"value":4} │ (2,4) │    2 │     4 │
└──────────────────────┴───────┴──────┴───────┘