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> )
Copy

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 |
+------------------------------------------------------------------+
Copy
SELECT ARRAY_POSITION('hi'::variant, array_construct('hello', 'hi'));
+---------------------------------------------------------------+
| ARRAY_POSITION('HI'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|---------------------------------------------------------------|
|                                                             1 |
+---------------------------------------------------------------+
Copy
SELECT ARRAY_POSITION('hello'::variant, array_construct('hola', 'bonjour'));
+----------------------------------------------------------------------+
| ARRAY_POSITION('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
|                                                                 NULL |
+----------------------------------------------------------------------+
Copy