I have some raw JSON data in ClickHouse table (actually, netflow V9 from netflow collector) it looks like this:
{"AgentID":"10.1.8.1",
"Header":{"Version":9,"Count":2},
"DataSets":[
[{"I":2,"V":"231"},{"I":3,"V":"151"},{"I":8,"V":"109.195.122.130"}],
[{"I":2,"V":"341"},{"I":3,"V":"221"},{"I":8,"V":"109.195.122.233"}]
]}'
My task is to convert DataSets arrays into another ClickHouse table in the following way:
I2 I3 I8
-----------------------------
231 151 109.195.122.130
341 221 109.195.122.233
...
To parse JSON consider using the specialized json functions:
(To get more about JSON parsing see How to extract json from json in clickhouse?)
The implementation above relies on the fixed structure of Datasets-array. As I understood in the real world this structure has an arbitrary schema (https://www.iana.org/assignments/ipfix/ipfix.xhtml), such as:
Thus it appears the question about a table with arbitrary columns count. ClickHouse doesn't support this feature - see how possible to present the table in this case https://stackoverflow.com/search?q=%5Bclickhouse%5D+pivot.