Subject: access sub elements using any()

From: Rob Sargent <[email-masked]>
Date: Mon, 31 Oct 2022 11:04:00 -0600

Given an array of arrays ( e.g. v = {{1,'a'},{2,'b'},{3,'c'},{2,'d'}} ) 
is it possible, in plain sql, to access the first element of the listed 
arrays using the IN function?  Say I wanted just those with 2 as first 
element.  "where 2 = any(v[1])" does not work and not sure it's 
supported.   And I cannot craft a lhs to fussy-match each of the 
elements in v.  Can this be done?

---

From: "Torsten Grust" <[email-masked]>
Date: Mon, 31 Oct 2022 23:36:56 +0100

Hi,

On Mon, Oct 31, 2022, at 18:04, Rob Sargent wrote:
> Given an array of arrays ( e.g. v = {{1,'a'},{2,'b'},{3,'c'},{2,'d'}} ) is it possible, in plain sql, to access the first element of the listed arrays using the IN function?  Say I wanted just those with 2 as first element.  "where 2 = any(v[1])" does not work and not sure it's supported.   And I cannot craft a lhs to fussy-match each of the elements in v.  Can this be done?

if your inner arrays would be row values instead, i.e. if v would read

  v = array[(1,'a'),(2,'b'),(3,'c'),(2,'d')] :: t[]
  assuming CREATE TYPE t AS (x int, y text);

then your query could simply be

  SELECT r.*
  FROM   unnest(v) AS r
  WHERE  r.x = 2;

Cheers,
  —Torsten

--
| Torsten Grust
| [email-masked]

---

From: Rob Sargent <[email-masked]>
Date: Mon, 31 Oct 2022 16:40:20 -0600

On 10/31/22 16:36, Torsten Grust wrote:
>> Given an array of arrays ( e.g. v = {{1,'a'},{2,'b'},{3,'c'},{2,'d'}} 
>> ) is it possible, in plain sql, to access the first element of the 
>> listed arrays using the IN function?  Say I wanted just those with 2 
>> as first element.  "where 2 = any(v[1])" does not work and not sure 
>> it's supported.   And I cannot craft a lhs to fussy-match each of the 
> | [email-masked] <mailto:[email-masked]>
>
>
Thank you!

---

From: Tom Lane <[email-masked]>
Date: Mon, 31 Oct 2022 20:50:06 -0400

Rob Sargent <[email-masked]> writes:
> arrays using the IN function?

If it's actually a 2-D array, and not an array-of-composite which is
what your sample data seems to suggest, then array slicing might help:

regression=# select ('{{1,2},{3,4},{5,6}}'::int[])[:][1];
     int4      
---------------
 {{1},{3},{5}}
(1 row)

regression=# select 2 = any (('{{1,2},{3,4},{5,6}}'::int[])[:][1]);
 ?column? 
----------
 f
(1 row)

regression=# select 3 = any (('{{1,2},{3,4},{5,6}}'::int[])[:][1]);
 ?column? 
----------
 t
(1 row)

			regards, tom lane