CODEWITHSUNDEEP
clickhouse
Iliyas B.
Iliyas B.

Reputation: 13

Clickhouse. Get value from json

I use Clickhouse database. There is a table with string column (data). All rows contains data like:

'[{"a":23, "b":1}]'
'[{"a":7, "b":15}]'

I wanna get all values of key "b".

1
15

Next query:

Select JSONExtractInt('data', 0, 'b') from table

return 0 all time. How i can get values of key "b"?

Upvotes: 1

Views: 2211

Answers (1)

Denny Crane
Denny Crane

Reputation: 13360

SELECT tupleElement(JSONExtract(j, 'Array(Tuple(a Int64, b Int64))'), 'b')[1] AS res
FROM
(
    SELECT '[{"a":23, "b":1}]' AS j
    UNION ALL
    SELECT '[{"a":7, "b":15}]'
)

┌─res─┐
│   1 │
└─────┘
┌─res─┐
│  15 │
└─────┘

Upvotes: 2

Related Questions