CODEWITHSUNDEEP
clickhouse
calebeaires
calebeaires

Reputation: 2084

Clickhouse convert table with json content each row into table

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

Upvotes: 0

Views: 1503

Answers (1)

Denny Crane
Denny Crane

Reputation: 13350

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 │
└──────────────────────┴───────┴──────┴───────┘

Upvotes: 1

Related Questions