Big Query: Join with function outcome (ERROR: Subquery in join predicate should only depend on exactly one join side.)

Question

I want to join two tables with using outcome of the function which I have written. Function returns a boolean.

However it says: Subquery in join predicate should only depend on exactly one join side.

What is the problem here?

Code:

CREATE TEMPORARY FUNCTION array_intersect(x ANY TYPE, y ANY TYPE) AS (
(
SELECT count(*) > 0
from 
        (
            select xe_1 from 
            (select JSON_EXTRACT_ARRAY(x) as xe), unnest(xe) as xe_1
        ) t1
 inner join
        (
            select ye_1 from 
            (select JSON_EXTRACT_ARRAY(y) as ye), unnest(ye) as ye_1
        ) t2 
on t1.xe_1 = t2.ye_1
)
);

WITH
k AS (
  SELECT
    FORMAT("%T", JSON_EXTRACT_ARRAY('["a","b","c"]')) AS x, date('2021-11-11') as date_x),

m as (
  SELECT
    FORMAT("%T", JSON_EXTRACT_ARRAY('["a","c"]')) AS y, date('2021-11-11') as date_y)


SELECT * from
 k t1
inner join 
(select * from m) t2 
on (select array_intersect(x_1, y_1) FROM (SELECT t1.x AS x_1, t2.y AS y_1))

Thank you very much =)

Answer 1

The clause on on the join requires and expression with 2 sides, I mean on x.a=x.b, you can not use just a boolean condition.

Also, there is an error in you logic. You are just sending a true that is not linked to any line on the on. Does not seams to be a case where you need a function. Use the join directly in the final query.

WITH
k AS (
  SELECT
    FORMAT("%T", JSON_EXTRACT_ARRAY('["a","b","c"]')) AS x, date('2021-11-11') as date_x),

m as (
  SELECT
    FORMAT("%T", JSON_EXTRACT_ARRAY('["a","c"]')) AS y, date('2021-11-11') as date_y)


select tx.*,ty.* from
(select * from m, unnest(JSON_EXTRACT_ARRAY(y)) as y_1) as ty
inner join
(select * from k, unnest(JSON_EXTRACT_ARRAY(x)) as x_1) as tx
on 
ty.y_1=tx.x_1

You can also remove redundancy with a DISTINCT sentence:

select distinct x,date_x,y,date_y from
(select * from m, unnest(JSON_EXTRACT_ARRAY(y)) as y_1) as ty
inner join
(select * from k, unnest(JSON_EXTRACT_ARRAY(x)) as x_1) as tx
on 
ty.y_1=tx.x_1