From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rob Sargent <robjsargent@gmail.com>
cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: access sub elements using any()
In-reply-to: <5fb866d1-8d2f-5118-18e2-9bbf227cbf1a@gmail.com>
References: <5fb866d1-8d2f-5118-18e2-9bbf227cbf1a@gmail.com>
Comments: In-reply-to Rob Sargent <robjsargent@gmail.com>
	message dated "Mon, 31 Oct 2022 11:04:00 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1046905.1667263806.1@sss.pgh.pa.us>
Date: Mon, 31 Oct 2022 20:50:06 -0400
Message-ID: <1046906.1667263806@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1046906.1667263806%40sss.pgh.pa.us>
Precedence: bulk

Rob Sargent <robjsargent@gmail.com> writes:
> 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?

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