Fix Error “searching for elements in multidimensional arrays is not supported” in PostgreSQL

If you’re getting a PostgreSQL error that reads “searching for elements in multidimensional arrays is not supported“, it’s probably because you’re trying to perform an operation against a multi dimensional array when that operation only supports one dimensional arrays.

To fix this issue, perform the operation against a one dimensional array instead. You may be able to do this by applying the unnest() function against the multi dimensional array.

Example of Error

Here’s an example of code that produces the error:

SELECT array_position(
    ARRAY[ ARRAY[ 'Cat', 'Dog', 'Horse' ], ARRAY[ 'Cow', 'Bat', 'Ant' ] ], 
    'Dog'
    );

Result:

ERROR:  searching for elements in multidimensional arrays is not supported

Here I used the array_position() function to search an array, but that function only works on one dimensional arrays. As expected, we get an error.

Solution

One solution is to only include a one dimensional array in our code:

SELECT array_position(
    ARRAY[ 'Cat', 'Dog', 'Horse' ], 
    'Dog'
    );

Result:

2

Another way we could deal with the issue is with the unnest() function:

SELECT array_position(
(SELECT ARRAY(
   SELECT unnest((ARRAY[[ 'Cat', 'Dog', 'Horse' ], [ 'Cow', 'Bat', 'Ant' ]])[1:2])
   )),
   'Bat'
   );

Result:

5