- Categories:
Semi-structured Data Functions (Array/Object)
ARRAY_POSITIONΒΆ
Returns the index of the first occurrence of an element in an array.
SyntaxΒΆ
ARRAY_POSITION( <variant_expr> , <array> )
ArgumentsΒΆ
variant_expr
This expression should evaluate to a VARIANT value. The function searches for the first occurrence of this value in the array.
array
The array to be searched.
ReturnsΒΆ
The data type of the returned value is INTEGER.
Usage NotesΒΆ
The return value is 0-based, not 1-based. In other words, if the
variant_expr
matches the first element in the array, this function returns 0, not 1.If the VARIANT value is not contained in the ARRAY, the function returns NULL.
If you specify NULL for
variant_expr
, the function returns the position of the first NULL in the array.
ExamplesΒΆ
The examples below show how to use this function:
SELECT ARRAY_POSITION('hello'::variant, array_construct('hello', 'hi')); +------------------------------------------------------------------+ | ARRAY_POSITION('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) | |------------------------------------------------------------------| | 0 | +------------------------------------------------------------------+SELECT ARRAY_POSITION('hi'::variant, array_construct('hello', 'hi')); +---------------------------------------------------------------+ | ARRAY_POSITION('HI'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) | |---------------------------------------------------------------| | 1 | +---------------------------------------------------------------+SELECT ARRAY_POSITION('hello'::variant, array_construct('hola', 'bonjour')); +----------------------------------------------------------------------+ | ARRAY_POSITION('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) | |----------------------------------------------------------------------| | NULL | +----------------------------------------------------------------------+