Postgres perform query to select array index

Question

How Can I perform a query that returns a row if I have the wanted values at same index on different columns ? For example, here is some code:

select id_reg, a1, a2 from lb_reg_teste2;
 id_reg |        a1        |     a2   
--------+------------------+-------------
      1 | {10,10,20,20,10} | {3,2,4,3,6}
(1 row)

The query would be someting like:

select id_reg from lb_reg_teste2 where idx(a1, '20') = idx(a2, '3');
# Should return id_reg = 1

I Found this script , but it only returns the first occurrence of a value in an array. For this case, I need all occurrences.

CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
  RETURNS int AS
$$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$$ LANGUAGE sql IMMUTABLE;

Answer 1

You can extract the values from the arrays along with their indices, and then filter out the results.

If the arrays have the same number of elements, consider this query:

SELECT id_reg,
       generate_subscripts(a1,1) as idx1,
       unnest(a1) as val1,
       generate_subscripts(a2,1) as idx2,
       unnest(a2) as val2
  FROM lb_reg_teste2

With the sample values of the question, this would generate this:

 id_reg | idx1 | val1 | idx2 | val2 
--------+------+------+------+------
      1 |    1 |   10 |    1 |    3
      1 |    2 |   10 |    2 |    2
      1 |    3 |   20 |    3 |    4
      1 |    4 |   20 |    4 |    3
      1 |    5 |   10 |    5 |    6

Then use it as a subquery and add a WHERE clause to filter out as necessary. For the example with 20 and 3 as the values to find at the same index:

SELECT DISTINCT id_reg FROM
   ( SELECT id_reg,
           generate_subscripts(a1,1) as idx1,
           unnest(a1) as val1,
           generate_subscripts(a2,1) as idx2,
           unnest(a2) as val2
      FROM lb_reg_teste2 ) s
    WHERE idx1=idx2 AND val1=20 AND val2=3;

If the number of elements of a1 and a2 differ, the subquery above will generate a cartesian product (NxM rows where N and M are the array sizes), so this will be less efficient but still produce the correct result, as far as I understood what you expect.

In this case, a variant would be to generate two distinct subqueries with the (values,indices) of each array and join them by the equality of the indices.