Is there a way to left join on column which is an Array

Question

I'm wondering if it's possible to make a left join on Array column, I know there is arrayJoin but I'm not sure if it suits my case.

I did try a query like this:

SELECT 
  foo.name AS name, -- String
  foo.policies AS policies -- Array(String),
  groupArray(profits7d.profits) AS profits7D
FROM (
  ...
) AS foo
any left join (
   SELECT 
     groupArray((x, y)) as profits,
     policy -- String
   FROM (
     SELECT
        day as x,
        SUM(profit) as y,
        policy
      FROM profits
      WHERE x >= toDateTime(NOW() - INTERVAL '1 week')
      GROUP BY x, policy
   )
   GROUP BY policy
) AS profits7d ON has(foo.policies, profits7d.policy)

I expect this:

┌─name─┬─policies──────┬─profits7D────────────┐
│ test │ ['one','two'] │ [(200,'2023-01-01')] │
└──────┴───────────────┴──────────────────────┘

I have a INVALID_JOIN_ON_EXPRESSION error, so I'm asking what I need to use to have this query to work, I can't find what expression works for ON

Thanks in advance

Cheers

Answer 1

Without arrayJoin

create table L (polices Array(String), f String) Engine=Memory as select ['a', 'b'], 'x';

create table R (policy String, f String) Engine=Memory as select * from values (('a', 'y'), ('b', 'y1'));

SELECT *
FROM L
ANY LEFT JOIN R ON 
  ((L.polices[1]) = R.policy) 
   OR ((L.polices[2]) = R.policy) 
   OR ((L.polices[3]) = R.policy) 
   OR ((L.polices[4]) = R.policy)

┌─polices───┬─f─┬─policy─┬─R.f─┐
│ ['a','b'] │ x │ a      │ y   │
└───────────┴───┴────────┴─────┘

With arrayJoin

SELECT
    L.polices,
    (any((L.f, R.*)) AS t).1 AS f,
    t.2 AS policy,
    t.3 AS f1
FROM
(
    SELECT
        arrayJoin(L.polices) AS _policy,
        polices,
        f
    FROM L
) AS L
ANY LEFT JOIN R ON _policy = R.policy
GROUP BY L.polices

┌─polices───┬─f─┬─policy─┬─f1─┐
│ ['a','b'] │ x │ a      │ y  │
└───────────┴───┴────────┴────┘