Collecting data from JSON array into ClickHouse table

Question

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
...

score 5 · Answer 1 · answered Feb 09 '21 at 09:29

To parse JSON consider using the specialized json functions:

SELECT
    toInt32(column_values[1]) AS I2,
    toInt32(column_values[2]) AS I3,
    column_values[3] AS I8
FROM 
(
    SELECT
        arrayJoin(JSONExtract(json, 'DataSets', 'Array(Array(Tuple(Int32, String)))')) AS row,
        arraySort(x -> (x.1), row) AS row_with_sorted_columns,
        arrayMap(x -> (x.2), row_with_sorted_columns) AS column_values
    FROM 
    (
        SELECT '{"AgentID":"10.1.8.1", "Header":{"Version":9,"Count":2}, "DataSets":[\n          [{"I":3,"V":"151"},{"I":8,"V":"109.195.122.130"},{"I":2,"V":"231"}],\n          [{"I":2,"V":"341"},{"I":3,"V":"221"},{"I":8,"V":"109.195.122.233"}]]}' AS json
    )
)


/*
┌─I2──┬─I3──┬─I8──────────────┐
│ 231 │ 151 │ 109.195.122.130 │
│ 341 │ 221 │ 109.195.122.233 │
└─────┴─────┴─────────────────┘
*/

(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:

{
   "AgentID":"192.168.21.15",
   "Header":{},
   "DataSets":[
      [
         {"I":8, "V":"192.16.28.217"},
         {"I":12, "V":"180.10.210.240"},
         {"I":5, "V":2},
         {"I":4, "V":6},
         {"I":7, "V":443},
         {"I":6, "V":"0x10"}
      ]
   ]
}

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.

Collecting data from JSON array into ClickHouse table

1 Answers1