How to check if an array is in a multidimensional array

Question

I'd want to check, using a filtering query, if an array is equal to an element of another multidimensional array which can be considered as an array of arrays.

For example:

Given the multidimensional array {{1,2}, {3,4}, {5,6}} I want to check if a one dimension array of one of the array elements.

Expected results:

Input: {1,2} or {3,4} -> Output: TRUE
Input: {2,3} or {1,5} -> Output: FALSE

I've already tried <@, but it returns TRUE for all the examples cases and I can't use ANY without slicing the multidimensional array.

Does anyone have a solution without using pgplsql?

Can ypu what hou have tried?

Aefits
– Aefits

2017-07-29 15:32:00 +00:00
Commented Jul 29, 2017 at 15:32 — Aefits
– Aefits, Commented Jul 29, 2017 at 15:32

Julia Leder · Accepted Answer · 2017-07-29 16:12:07Z

1

This does seem like a difficult problem to solve without any pgpsql. However, if this function is utilized, it is much simpler: https://wiki.postgresql.org/wiki/Unnest_multidimensional_array

CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray AS
$function$
DECLARE
    s $1%TYPE;
BEGIN
    FOREACH s SLICE 1  IN ARRAY $1 LOOP
        RETURN NEXT s;
    END LOOP;
    RETURN;
END;
$function$
LANGUAGE plpgsql IMMUTABLE;

To use:

 create table array_test (arr integer[][]);
 insert into array_test (select '{{1,2}, {3,4}, {5,6}}');

 select (case when '{1,2}' in (select reduce_dim(arr) from array_test) then true 
         else false end);
 case
 ------
 t
(1 row)

select (case when '{1,4}' in (select reduce_dim(arr) from array_test) then true 
         else false end);
 case
------
 f
(1 row)

answered Jul 29, 2017 at 16:12

Julia Leder

8265 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abelisto · Accepted Answer · 2017-07-29 16:12:33Z

1

Simple way: search in array like in string:

select '{{1, 2}, {3, 4}, {5, 6}}'::int[][]::text like '%{1,2}%';

Complex way: decompose array to slices (without plpgsql):

with t(x) as (values('{{1, 2}, {3, 4}, {5, 6}}'::int[][]))
select *
from t
where (
  select bool_or(x[s:s] = '{{1,3}}') from generate_subscripts(x,1) as s);

answered Jul 29, 2017 at 16:12

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to check if an array is in a multidimensional array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related