I am trying to use all the values of a table as an input for an SQL defined function.
SELECT Field1, REGEXP_CONTAINS(Field1, (SELECT Field2 FROM table2)) FROM table1;
This one returns an error in big query because:
Can you please tell me if there is a way to use all the values of one table as an input for a single function.
Best regards,
Below example is for BigQuery Standard SQL
#standardSQL
SELECT
Field1, STRING_AGG(Field2) AS contained
FROM `table1`
JOIN `table2`
ON REGEXP_CONTAINS(Field1, Field2)
GROUP BY Field1
you can test / play with above using below dummy data
#standardSQL
WITH `table1` AS (
SELECT 'abc' Field1 UNION ALL
SELECT 'xyz'
),
`table2` AS (
SELECT 'a' Field2 UNION ALL
SELECT 'x' UNION ALL
SELECT 'y' UNION ALL
SELECT 'z'
)
SELECT
Field1, STRING_AGG(Field2) AS contained
FROM `table1`
JOIN `table2`
ON REGEXP_CONTAINS(Field1, Field2)
GROUP BY Field1
with result as
Field1 contained
abc a
xyz x,y,z
Second Option is more inline with your original expectation (in terms of syntax pattern) but requires use of
SQL UDF:
#standardSQL
CREATE TEMP FUNCTION Check_Contains(str STRING, arr ARRAY<STRING>) AS ((
SELECT STRING_AGG(item) AS contained
FROM UNNEST(arr) item
WHERE REGEXP_CONTAINS(str, item)
));
SELECT Field1,
Check_Contains(Field1, ARRAY(SELECT Field2 FROM `table2`)) AS contained
FROM `table1`
To test / play - you can use below
#standardSQL
CREATE TEMP FUNCTION Check_Contains(str STRING, arr ARRAY<STRING>) AS ((
SELECT STRING_AGG(item) AS contained
FROM UNNEST(arr) item
WHERE REGEXP_CONTAINS(str, item)
));
WITH `table1` AS (
SELECT 'abc' Field1 UNION ALL
SELECT 'xyz' UNION ALL
SELECT 'vwu'
),
`table2` AS (
SELECT 'a' Field2 UNION ALL
SELECT 'x' UNION ALL
SELECT 'y' UNION ALL
SELECT 'z'
)
SELECT Field1,
Check_Contains(Field1, ARRAY(SELECT Field2 FROM `table2`)) AS contained
FROM `table1`
with output as
Field1 contained
abc a
xyz x,y,z
vwu null
One more option (without UDF)
#standardSQL
SELECT Field1,
ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(Field1, (SELECT STRING_AGG(Field2, '|') FROM `table2`)), ',') AS contained
FROM `table1`
you can use same dummy data as for above (first two options)
You need to use a CROSS JOIN instead. Note that this will be slow if the tables are large, since you're taking the cross product of all the rows:
SELECT
t1.Field1,
REGEXP_CONTAINS(t1.Field1, t2.Field2)
FROM table1 AS t1
CROSS JOIN table2 AS t2;
If you want to know all of the Field1/Field2 pairs for which this expression is true, you can use a query with a filter:
SELECT
t1.Field1,
t2.Field2
FROM table1 AS t1
CROSS JOIN table2 AS t2
WHERE REGEXP_CONTAINS(t1.Field1, t2.Field2);
Thank you all for your answers. I have a similar version to big query and ended up using the following code which is close to your suggestions.
WITH table1 AS (SELECT field1 FROM UNNEST(['value1', ...'value2']) AS field1),
table2 AS (SELECT field2 FROM UNNEST(['v1', 'v2']) AS field2)
SELECT
field1,
(SELECT LOGICAL_OR(REGEXP_CONTAINS(field1, CONCAT(r'(?i)\b', field2, r'\b'))) FROM table2) AS has_table2_match
FROM
table1
Thank you again for your suggestions. Appreciate it.
