Feedback-ID: i51c14773:Fastmail
User-Agent: Cyrus-JMAP/3.7.0-alpha0-1087-g968661d8e1-fm-20221021.001-g968661d8
Mime-Version: 1.0
Message-Id: <31612ff3-67a4-4781-b359-0d182f73fe84@betaapp.fastmail.com>
In-Reply-To: <5fb866d1-8d2f-5118-18e2-9bbf227cbf1a@gmail.com>
References: <5fb866d1-8d2f-5118-18e2-9bbf227cbf1a@gmail.com>
Date: Mon, 31 Oct 2022 23:36:56 +0100
From: "Torsten Grust" <teggy@fastmail.com>
To: "Rob Sargent" <robjsargent@gmail.com>,
 pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: access sub elements using any()
Content-Type: multipart/alternative;
 boundary=f18ba19a0be34eb2ab9f8107263433d2
Archived-At: 
 <https://www.postgresql.org/message-id/31612ff3-67a4-4781-b359-0d182f73fe84%40betaapp.fastmail.com>
Precedence: bulk

--f18ba19a0be34eb2ab9f8107263433d2
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi,

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

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

  v =3D 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 =3D 2;

Cheers,
  =E2=80=94Torsten

--
| Torsten Grust
| teggy@fastmail.com


--f18ba19a0be34eb2ab9f8107263433d2
Content-Type: text/html;charset=utf-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>Hi,<br></div><d=
iv><br></div><div>On Mon, Oct 31, 2022, at 18:04, Rob Sargent wrote:<br>=
</div><div><blockquote type=3D"cite" id=3D"qt" style=3D""><span class=3D=
"font" style=3D"font-family:&quot;Courier New&quot;, Courier, monospace;=
">Given an array of
      arrays ( e.g. v =3D {{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?&nbsp; Say I wanted just those with 2=
 as
      first element.&nbsp; "where 2 =3D any(v[1])" does not work and not=
 sure
      it's supported.&nbsp;&nbsp; And I cannot craft a lhs to fussy-matc=
h each of
      the elements in v.&nbsp; Can this be done?</span><br></blockquote>=
<div><br></div><div>if your inner arrays would be row values instead, i.=
e. if v would read<br></div></div><div><br></div><div>&nbsp; v =3D array=
[(1,'a'),(2,'b'),(3,'c'),(2,'d')] :: t[]<br></div><div>&nbsp; assuming C=
REATE TYPE t AS (x int, y text);<br></div><div><br></div><div>then your =
query could simply be<br></div><div><br></div><div>&nbsp; SELECT r.*<br>=
</div><div>&nbsp; FROM&nbsp;&nbsp; unnest(v) AS r<br></div><div>&nbsp; W=
HERE&nbsp; r.x =3D 2;</div><div><br></div><div>Cheers,<br></div><div>&nb=
sp; =E2=80=94Torsten<br></div><div><br></div><div id=3D"sig115172376"><d=
iv class=3D"signature"><span class=3D"font" style=3D"font-family:menlo, =
consolas, monospace, sans-serif;">--</span><span class=3D"font" style=3D=
"font-family:menlo, consolas, monospace, sans-serif;"></span><br></div><=
div class=3D"signature"><span class=3D"font" style=3D"font-family:menlo,=
 consolas, monospace, sans-serif;">| Torsten Grust</span><span class=3D"=
font" style=3D"font-family:menlo, consolas, monospace, sans-serif;"></sp=
an><br></div><div class=3D"signature"><span class=3D"font" style=3D"font=
-family:menlo, consolas, monospace, sans-serif;">| </span><a href=3D"mai=
lto:teggy@fastmail.com"><span class=3D"font" style=3D"font-family:menlo,=
 consolas, monospace, sans-serif;">teggy@fastmail.com</span></a><span cl=
ass=3D"font" style=3D"font-family:menlo, consolas, monospace, sans-serif=
;"></span><br></div><div class=3D"signature"><br></div></div><div><br></=
div></body></html>
--f18ba19a0be34eb2ab9f8107263433d2--