Ask Your Question
4

How can I convert a JSON array into rows in ClickHouse?

asked 2022-12-05 11:00:00 +0000

lalupa gravatar image

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2022-04-20 19:00:00 +0000

scrum gravatar image

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.

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss

Add Answer


Question Tools

Stats

Asked: 2022-12-05 11:00:00 +0000

Seen: 23 times

Last updated: Apr 20 '22