Snowflake. How to check if the sub-array is presented in another array?

user_10823769 0
Viewed 132

I want to check if all the items from one array are presented in another array. I've been looking through Snowflake docs but can't find any good approach. For example in PostgreSQL it's easily solved by <@ (i.e. ARRAY[2,7] <@ ARRAY[1,7,4,2,6]). Any ideas on how to solve it gracefully without ugly hacks? Thanks!

snowflake-cloud-data-platform sql
2 Answers

The logic with ARRAY_INTERSECTION and size could be wrapped with UDF:

CREATE FUNCTION is_array_subarray (arr1 ARRAY, arr2 ARRAY)
  RETURNS BOOLEAN
  AS 'ARRAY_SIZE(ARRAY_INTERSECTION(arr1,arr2)) = ARRAY_SIZE(arr1)';

Query:

SELECT a1, a2, is_array_subarray (a1, a2) AS res
FROM (SELECT ARRAY_CONSTRUCT(2,7), ARRAY_CONSTRUCT(1,7,4,2,6) 
      UNION
      SELECT ARRAY_CONSTRUCT(2,7,2), ARRAY_CONSTRUCT(1,7,4,2,6) 
     ) AS s(a1, a2);

Output:

enter image description here

(*) The handling of non-unique elements may require refinement depending of needs. Here it requires full match

user_50708790

A small addition to Lukasz Szozda's answer. It's much better to use ARRAY_SIZE(ARRAY_INTERSECTION(arr1,arr2)) = ARRAY_SIZE(arr1) as it is, without using UDF because of really high performance impact. I was playing around with the 2 equal queries but the latter was with the UDF. The time of execution difference was 40ms and 2s. I decided not to use UDF.

user_108237690
Related