How to check multidimensional array of arrays contains array?

Question

I have an array of arrays [ID, TYPE].

All values are integer. Array can be integer[] or jsonb type.

SELECT array[[442,2],[443,2]]

SELECT to_jsonb(array[[1,2],[3,4]])

How can I check this array(s) contains array [443,2]?

EDIT One of the way to check described here.

1# SELECT array[[442,2],[443,2]] @> array[443,2] -- returns True
2# SELECT array[[442,2],[443,2]] @> array[2,443] -- returns True too

For my case, I want get False in 2#

Answer 1

Technically there is no such thing as a two-dimensional array in Postgres. Quote from the manual:

The current implementation does not enforce the declared number of dimensions either. [...] So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior

So the value array[[442,2],[443,2]] is internally stored as array[442,2,443,2] and that's the reason why array[[442,2],[443,2]] @> array[2,443] is true, because it is internally represented as array[442,2,443,2] @> array[2,443] which obviously is true.

You can however do that if you declare an array of a row type.

create type idtype as (id integer, type integer);

Then you can do the following:

select array[(442,2), (443,2)]::idtype[] @> array[(442,2)]::idtype[];
--> true

but

select array[(442,2), (443,2)]::idtype[] @> array[(2,442)]::idtype[];
--> false

Answer 2

Potential Workaround with JSONB

As a potential workaround to PostgreSQL not respecting dimensionality, you can use JSONB. As a caveat though the containment operator (@>) isn't looking for a match based on an "array". It's decomposing the array and looking for a match on the elements. So these return true

SELECT '[[4,5]]'::jsonb @> '[[5,4]]';
SELECT '[[4,3,5]]'::jsonb @> '[[5,4]]';
SELECT '[[1,2],[4,3,5],[0]]'::jsonb @> '[[5,4]]';

but this returns false,

SELECT '[[1,2],[4,3,5],[0]]'::jsonb @> '[[5,0]]';