Revision history [back]

You can use the JSONEachRow function in ClickHouse to convert a JSON array into rows. This function takes a string or a JSON object as input and returns a table with one row for each element in the array. Here's an example:

Assume you have the following JSON string:

'["apple", "orange", "banana"]'

To convert this JSON array into rows in ClickHouse, you can use the JSONEachRow function in a query:

SELECT * FROM JSONEachRow('["apple", "orange", "banana"]')

This will return a table with one row for each element in the array:

┌─key─┬─value─┐
│ 1   │ apple │
│ 2   │ orange│
│ 3   │ banana│
└─────┴───────┘

You can also use this function directly on a column containing JSON arrays. For example, if you have a table fruits with a column names containing JSON arrays of fruit names:

┌────┬─────────────────────┐
│ id │ names               │
├────┼─────────────────────┤
│  1 │ ["apple", "orange"] │
│  2 │ ["banana", "kiwi"]  │
└────┴─────────────────────┘

You can use JSONEachRow to expand the arrays into rows:

SELECT id, value FROM fruits
ARRAY JOIN JSONEachRow(names) AS value

┌─id─┬─value─┐
│  1 │ apple │
│  1 │ orange│
│  2 │ banana│
│  2 │ kiwi  │
└────┴───────┘

This will return a table with one row for each array element, with the original id value and the expanded value column.