contains

On this page

Tests whether an array contains a value.

Syntax

contains(<array>, <value>)

Arguments

ArgumentTypeDescription
<array>arrayArray to search.
<value>Any typeValue to find. NULL matches a NULL element.

Returns

Returns boolean: TRUE if the value is present, otherwise FALSE. Returns NULL only if the input array is NULL.

Examples

Select events carrying a label

Filter a collection of event labels by exact element membership.

VALUES
    ('evt-17', ['checkout', 'timeout']),
    ('evt-42', ['checkout', 'completed']),
    ('evt-63', ['timeout-warning'])
SELECT $0 AS event_id, $1 AS labels
WHERE contains(labels, 'timeout')
SELECT event_id, labels
+----------+------------------------+
| event_id | labels                 |
+----------+------------------------+
| evt-17   | ["checkout","timeout"] |
+----------+------------------------+

timeout-warning is a different element; array membership is not substring matching.

Find incomplete event paths

A NULL element can mark an event whose name was not captured. Detect those paths explicitly.

VALUES
    ('s-17', ['cart_viewed', NULL, 'payment_failed']),
    ('s-42', ['cart_viewed', 'payment_completed'])
SELECT $0 AS session_id, $1 AS journey
SELECT session_id, contains(journey, NULL) AS has_missing_step
+------------+------------------+
| session_id | has_missing_step |
+------------+------------------+
| s-17       | true             |
| s-42       | false            |
+------------+------------------+

A NULL search value matches a NULL array element. This differs from comparing scalar values with = NULL.