Re: access sub elements using any()

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
| teggy(at)fastmail(dot)com