Clickhouse. Get value from json

Question

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"?

Answer 1

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