ARRAY_CONTAINS

Syntax

ARRAY_CONTAINS(array1, value_to_check)

AMC specifics

• Handy for finding users whose collected values contain a given product or format.
• The value is compared with the array, not with a sensitive column; AMC does reject literal comparisons on columns with a high threshold.

Example

Users with at least one Sponsored Brands row.

WITH paths AS (
  SELECT
    user_id,
    COLLECT(DISTINCT ad_product_type) AS products
  FROM
    sponsored_ads_traffic
  GROUP BY
    1
)
SELECT
  COUNT(DISTINCT user_id) AS users_with_sponsored_brands
FROM
  paths
WHERE
  ARRAY_CONTAINS(products, 'sponsored_brands')

Checked against the AMC schema, not executed on an AMC instance.