Can someone explain how arrayJoin() function works in Clickhouse database?

user_13003484 0
Viewed 427

I am a newbie to ClickHouse DB and the example provided in the documentation doesn't help in properly understanding the concept. Explanation with how arrayjoin() can be used with simple scenarios would be appreciated.

clickhouse database
1 Answers

Let's consider the next scenarios:

  1. when need to transform Array to a relation (set of rows)
/* get error */
SELECT 1
WHERE 1 IN ([1, 2]);

/* ok */
SELECT 1
WHERE 1 IN (SELECT arrayJoin([1, 2]));

/* get error */
SELECT *
FROM (SELECT [1, 2] a)
WHERE a = 2;

/* ok */
SELECT *
FROM (SELECT arrayJoin([1, 2]) a)
WHERE a = 2;
  1. to unfold/flatten the rows
SELECT
    metric_id,
    metric_name,
    arrayJoin(metric_values) AS metric_value
FROM
( /* test data */
    SELECT
        1 AS metric_id,
        'name_1' AS metric_name,
        [1, 4, 55] AS metric_values
    UNION ALL
    SELECT
        2 AS metric_id,
        'name_2' AS metric_name,
        [-7, 11] AS metric_values
)
/* result
┌─metric_id─┬─metric_name─┬─metric_value─┐
│         1 │ name_1      │            1 │
│         1 │ name_1      │            4 │
│         1 │ name_1      │           55 │
│         2 │ name_2      │           -7 │
│         2 │ name_2      │           11 │
└───────────┴─────────────┴──────────────┘
*/

/* produce Cartesian product */
SELECT
    arrayJoin([1, 2]) AS n,
    arrayJoin(['a', 'b']) AS ll,
    arrayJoin(['A', 'B']) AS ul
/* result
┌─n─┬─ll─┬─ul─┐
│ 1 │ a  │ A  │
│ 1 │ a  │ B  │
│ 1 │ b  │ A  │
│ 1 │ b  │ B  │
│ 2 │ a  │ A  │
│ 2 │ a  │ B  │
│ 2 │ b  │ A  │
│ 2 │ b  │ B  │
└───┴────┴────┘
*/

/* flatten the multidimension array */
SELECT arrayJoin(arrayJoin([[1, 2], [3, 4]])) AS d
/* result
┌─d─┐
│ 1 │
│ 2 │
│ 3 │
│ 4 │
└───┘
*/

When you need to chain arrays item by item instead of getting cartesian product consider using ARRAY JOIN:

/* cartesian product */

SELECT
    arrayJoin(arr1),
    arrayJoin(arr2)
FROM
(
    SELECT
        [1, 2] AS arr1,
        [11, 22] AS arr2
)

/*
┌─arrayJoin(arr1)─┬─arrayJoin(arr2)─┐
│               1 │              11 │
│               1 │              22 │
│               2 │              11 │
│               2 │              22 │
└─────────────────┴─────────────────┘
*/


/* connect array's item one by one */

SELECT a1, a2, arr1, arr2
FROM
(
    SELECT
        [1, 2] AS arr1,
        [11, 22] AS arr2
)
ARRAY JOIN arr1 as a1, arr2 as a2

/*
┌─a1─┬─a2─┬─arr1──┬─arr2────┐
│  1 │ 11 │ [1,2] │ [11,22] │
│  2 │ 22 │ [1,2] │ [11,22] │
└────┴────┴───────┴─────────┘
*/
user_3032980
Related