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.

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 |
+----------------------------------------------------------------------+