sql - 将 ClickHouse 中嵌套类型的不同键值对收集到数组中

Question

我在 ClickHouse 中有以下架构的数据:

CREATE TABLE table AS (
     key String,
     …
     nested Nested (
            key String,
            value String
     )
) …

一些示例数据:

key | … | nested                        |
----|---|-------------------------------|
k1  |   | [{"key": "a", "value": "1"}]  |
k1  |   | [{"key": "a", "value": "2"}]  |
k1  |   | [{"key": "a", "value": "1"},  |
    |   |   "key": "a", "value": "2"}]  |
k1  |   | [{"key": "b", "value": "3"    |

我想按键分组并将所有不同的键值对收集到两个数组中:

key   |  nested.key     |  nested.value    |
------|-----------------|------------------|
k1    | ["a", "a", "b"] |  ["1", "2", "3"] |

在 ClickHouse 中执行此操作的最简单、最有效的方法是什么？

Answer 1

我会建议这个查询:

SELECT DISTINCT
    key,
    arrayDistinct(groupArray((nested.key, nested.value))) AS distinctNested,
    arrayMap(x -> (x.1), distinctNested) AS `nested.keys`,
    arrayMap(x -> (x.2), distinctNested) AS `nested.values`
FROM test.table_002
ARRAY JOIN nested
GROUP BY key

/* Result
┌─key─┬─distinctNested──────────────────┬─nested.keys───┬─nested.values─┐
│ k1  │ [('a','1'),('a','2'),('b','3')] │ ['a','a','b'] │ ['1','2','3'] │
└─────┴─────────────────────────────────┴───────────────┴───────────────┘
*/

/* Test data preparing */

CREATE TABLE test.table_002 (
  key String,
  nested Nested (key String, value String)
) ENGINE = Memory;

INSERT INTO test.table_002
FORMAT JSONEachRow
{"key": "k1", "nested.key":["a"], "nested.value": ["1"]}
{"key": "k1", "nested.key":["a"], "nested.value": ["2"]}
{"key": "k1", "nested.key":["a", "a"], "nested.value": ["1", "2"]}
{"key": "k1", "nested.key":["b"], "nested.value": ["3"]}