intersect_arrays

Description

Returns an array containing the matching elements inside the two input arrays.

The multiplicity of a value in the output array will be the minimum multiplicity of that value in the two input arrays. The function is NULL-safe, meaning it treats NULLS as known values for comparing equality: if both input arrays contain a NULL value, then the resulting output array will also contain a NULL.

Return type

generic array

Domain

This is a scalar function (calculates a single output value for a single input row.)

Categories

Usage

intersect_arrays(array_1, array_2)

Argument

Type

Optional

Repeatable

Restrictions

array_1

array

no

no

none

array_2

array

no

no

none

Examples

make_col foo:intersect_arrays(make_array('red', 'green'), make_array('green', 'blue'))

Make the column ‘foo’ with array ['green'].

make_col foo:intersect_arrays(make_array(0, 0, 0, 1, 2), make_array(0, 0, 1, 3))

Make the column ‘foo’ with array [0, 0, 1].

make_col foo:intersect_arrays(make_array('hello', variant_null()), make_array('goodbye', variant_null()))

Make the column ‘foo’ with array [null].