CONTAINS

Returns true if the specified element is found in the collection and false if not. This function uses null-safe equality checks when testing elements.

Behavior type

Syntax

CONTAINS(collection, { value | lambda-expression })

Arguments

collection

Target collection (ARRAY or SET).

value

Value to search for; type must match or be coercible to the element type of the collection.

lambda-expression

Lambda function to apply to each element. The function must return a Boolean value. The first argument to the function is the element, and the optional second element is the index of the element.

Examples

=> SELECT CONTAINS(SET[1,2,3,4],2);
 contains
----------
t
(1 row)

You can search for NULL as an element value:

=> SELECT CONTAINS(ARRAY[1,null,2],null);
 contains
----------
 t
(1 row)

You can search for complex element types:

=> SELECT CONTAINS(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]],
                   ARRAY[1,2,3]);
 CONTAINS
----------
 t
(1 row)

=> SELECT CONTAINS(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]],
                   ARRAY[1,null,4]);
 CONTAINS
----------
 t
(1 row)

The second example, comparing arrays with null elements, returns true because CONTAINS uses a null-safe equality check when evaluating elements.

In the following example, the orders table has the following definition:

=> CREATE EXTERNAL TABLE orders(
  orderid int,
  accountid int,
  shipments Array[
    ROW(
      shipid int,
      address ROW(
        street varchar,
        city varchar,
        zip int
      ),
      shipdate date
    )
  ]
 ) AS COPY FROM '...' PARQUET;

The following query tests for a specific order. When passing a ROW literal as the second argument, cast any ambiguous fields to ensure type matches:

=> SELECT CONTAINS(shipments,
            ROW(1,ROW('911 San Marcos St'::VARCHAR,
                  'Austin'::VARCHAR, 73344),
            '2020-11-05'::DATE))
   FROM orders;
 CONTAINS
----------
 t
 f
 f
(3 rows)

Lambdas

Consider a table of departments where each department has an array of ROW elements representing employees. The following query finds departments with early hires (low employee IDs):

=> SELECT deptID FROM departments
   WHERE CONTAINS(employees, e -> e.id < 20);
 deptID
--------
      1
      3
(2 rows)

In the following example, a schedules table includes an array of events, where each event is a ROW with several fields:

=> CREATE TABLE schedules
       (guest VARCHAR,
       events ARRAY[ROW(e_date DATE, e_name VARCHAR, price NUMERIC(8,2))]);

You can use the CONTAINS function with a lambda expression to find people who have more than one event on the same day. The second argument, idx, is the index of the current element:

=> SELECT guest FROM schedules
WHERE CONTAINS(events, (e, idx) ->
                       (idx < ARRAY_LENGTH(events) - 1)
                       AND (e.e_date = events[idx + 1].e_date));
    guest
-------------
 Alice Adams
(1 row)

See also

• ARRAY_FIND
• FILTER