How to convert Json array to array of tuples in Clickhouse

Question

I have a Json array

{ "foo": [ { "a": "bar", "b": 10, "c": 100 } ] }

How to convert it to an array of tuples?

Array(Tuple(String, Int64))

where String is a and Int64 is b?

Answer 1

I'm assuming you have this as a string in an existing column, if so JSONExtract https://clickhouse.com/docs/en/sql-reference/functions/json-functions#jsonextractjson-indices_or_keys-return_type will do what you need i.e.

SELECT JSONExtract('[{"a":"bar","b":10, "c":100}]', 'Array(Tuple(a String, b Int64, c Int64))')

Query id: ec45c3ce-a5fd-4d70-83a9-4e94b32044ca

   ┌─JSONExtract('[{"a":"bar","b":10, "c":100}]', 'Array(Tuple(a String, b Int64, c Int64))')─┐
1. │ [('bar',10,100)]                                                                         │
   └──────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.
────────────────────────────────────────────┘

If you have a file e.g.

{"foo":[{"a":"bar","b":10, "c":100}], "other": "a"}

You can either rely on schema inference e.g.

SELECT
    *,
    toTypeName(foo)
FROM file('temp.json')

Query id: f3a0833a-59eb-47cf-9190-59b47dbe91ee

   ┌─foo──────────────┬─other─┬─toTypeName(foo)──────────────────────────────────────────────────────────────────┐
1. │ [('bar',10,100)] │ a     │ Array(Tuple(
    a Nullable(String),
    b Nullable(Int64),
    c Nullable(Int64))) │
   └──────────────────┴───────┴──────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

or specify directly

SELECT *
FROM file('temp.json', 'JSONEachRow', 'foo Array(Tuple(a String, b Int64, c Int64)), other String')


   ┌─foo──────────────┬─other─┐
1. │ [('bar',10,100)] │ a     │
   └──────────────────┴───────┘

1 row in set. Elapsed: 0.002 sec