Content-Type: multipart/alternative;
 boundary="------------YFN4F3rTxif0JvMgQf28yLPq"
Message-ID: <f4c25456-1c4c-a279-03d8-3675fe9c2cf3@gmail.com>
Date: Mon, 31 Oct 2022 16:40:20 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.2.2
Subject: Re: access sub elements using any()
Content-Language: en-CA
To: Torsten Grust <teggy@fastmail.com>,
 pgsql-sql <pgsql-sql@lists.postgresql.org>
References: <5fb866d1-8d2f-5118-18e2-9bbf227cbf1a@gmail.com>
 <31612ff3-67a4-4781-b359-0d182f73fe84@betaapp.fastmail.com>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: <31612ff3-67a4-4781-b359-0d182f73fe84@betaapp.fastmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/f4c25456-1c4c-a279-03d8-3675fe9c2cf3%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------YFN4F3rTxif0JvMgQf28yLPq
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 10/31/22 16:36, Torsten Grust wrote:
> 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@fastmail.com <mailto:teggy@fastmail.com>
>
>
Thank you!

--------------YFN4F3rTxif0JvMgQf28yLPq
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 10/31/22 16:36, Torsten Grust wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:31612ff3-67a4-4781-b359-0d182f73fe84@betaapp.fastmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <title></title>
      <style type="text/css">p.MsoNormal,p.MsoNoSpacing{margin:0}</style>
      <div>Hi,<br>
      </div>
      <div><br>
      </div>
      <div>On Mon, Oct 31, 2022, at 18:04, Rob Sargent wrote:<br>
      </div>
      <div>
        <blockquote type="cite" id="qt" style=""><span class="font"
            style="font-family:&quot;Courier New&quot;, Courier,
            monospace;">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?</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>  v = array[(1,'a'),(2,'b'),(3,'c'),(2,'d')] :: t[]<br>
      </div>
      <div>  assuming CREATE 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>  SELECT r.*<br>
      </div>
      <div>  FROM   unnest(v) AS r<br>
      </div>
      <div>  WHERE  r.x = 2;</div>
      <div><br>
      </div>
      <div>Cheers,<br>
      </div>
      <div>  —Torsten<br>
      </div>
      <div><br>
      </div>
      <div id="sig115172376">
        <div class="signature"><span class="font"
            style="font-family:menlo, consolas, monospace, sans-serif;">--</span><span
            class="font" style="font-family:menlo, consolas, monospace,
            sans-serif;"></span><br>
        </div>
        <div class="signature"><span class="font"
            style="font-family:menlo, consolas, monospace, sans-serif;">|
            Torsten Grust</span><span class="font"
            style="font-family:menlo, consolas, monospace, sans-serif;"></span><br>
        </div>
        <div class="signature"><span class="font"
            style="font-family:menlo, consolas, monospace, sans-serif;">|
          </span><a href="mailto:teggy@fastmail.com"
            moz-do-not-send="true"><span class="font"
              style="font-family:menlo, consolas, monospace,
              sans-serif;">teggy@fastmail.com</span></a><span
            class="font" style="font-family:menlo, consolas, monospace,
            sans-serif;"></span><br>
        </div>
        <div class="signature"><br>
        </div>
      </div>
      <div><br>
      </div>
    </blockquote>
    <font face="Courier New, Courier, monospace">Thank you!<br>
      <br>
    </font>
  </body>
</html>

--------------YFN4F3rTxif0JvMgQf28yLPq--