Content-Type: multipart/alternative;
 boundary="------------mJjCjgQgAKQVSfqcp9bI35ec"
Message-ID: <4a527da4-7fc3-4c1c-a59b-dd08e405eb38@dunslane.net>
Date: Tue, 4 Mar 2025 13:13:39 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2023 JSON simplified accessor support
To: Mark Dilger <mark.dilger@enterprisedb.com>,
 Alexandra Wang <alexandra.wang.oss@gmail.com>
Cc: Matheus Alcantara <matheusssilv97@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 Nikita Glukhov <glukhov.n.a@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 "David E. Wheeler" <david@justatheory.com>,
 jian he <jian.universality@gmail.com>
References: <d2d872c3-84f9-48f6-9963-f4537f02bc91@eisentraut.org>
 <adb15d59-2e6b-4d2e-ac5b-7aaea41f738f@gmail.com>
 <CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
 <b24dc997-d78f-47bd-b323-14e68232d127@dunslane.net>
 <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org>
 <CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
 <CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com>
 <CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com>
 <CAK98qZ1rZaVNy6ViangQom4iinZVH7=ebqhTsPxMQbN5ZtE9XQ@mail.gmail.com>
 <CAFY6G8cY5SUG5L-0ryVpom6HynE49p8-XQ59qkaEgnZZJ-c4Rg@mail.gmail.com>
 <CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>
 <CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com>
 <CAHgHdKtL9nNaKXGCLt9gWugVzYWKhoBDQ7NESUwdCBty8kFK-A@mail.gmail.com>
 <CAHgHdKubGHcJiZ6iVoBty5FwF4f+3uE0M00Er4tTsh4_LaH_xw@mail.gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAmwQTAQgARQIbAwIX
 gAIZAQULCQgHAgMiAgEGFQoJCAsCBBYCAwECHgcWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUC
 ZFlxxwUJGVGAbgAKCRCZ+n/LWfw7gXikB/9ZdcUy6CTBFIIuL/bVsc1eLEW/gJBjJBF6HxNY
 xgEkAgXAp4Lg4A5U+QB9GouFr7+GYxF0BU4hzoGhNPUWltxnHdMWP8nC/38LAqgMi8L/bbsm
 HW5YPBdWYaAZAPJQVfOAgjTbRUb26KSprpyrrJKW0ZmrZfjhNPcQ72jpWzoPLQqx2X6B0fru
 1jq+cBh8lb6r1mJTim1T3JIn+F/v5VpdQS+EL8xqsHkfzKjIPsW3CIXpkypSk6saA55Rkkbl
 26AW8ftPVB0Q6Lnn6FLt9CP0MGNixBQ55yq8r1K+nCBvCCjvQjM8RDm0UUum0WNl+ifQgTLO
 E8TWEnwVtkBf+3QWzsBNBE7KWFkBCADRnOM0FCzsYW6jtncg+dWIagjUZpvaClmqn/sJluLa
 Q3v1VXMQJzYs3eC1gh386W+XBwLRpDj3jzH81lX+p73Re3d3oJW7X+ffsxuzu5ZVdMUkqBYo
 nkAbKxr6gyJ12F/+JkUVzLcoTN+d/7YsQvUVi7NaKH8mJgjz112O4fUe3p9wfAaFa0RXHc5S
 GPzRTYRRlv/XZBIho4J2tkZOnteZJZ+GbxQVlINt6fd8P6al3MWOvpP/ExJPguEfjOsO6Njy
 xjo3WfpD4lHMOR/Oc3/8mScEF84rF2jXbsFgelWnbPWAvXY+pD0dXOFRkagGmC/viwBDqq5b
 5tk76kKmUbZxABEBAAHCwHwEGAEIACYCGwwWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUCZFlx
 5wUJGVGAjgAKCRCZ+n/LWfw7gf+iB/4g8CPY5jihf5r/8EsoIGe2H+dpVmpPF8YGBzTIvCz/
 fQoOq8AX/pE76QEuFnFZWfjw+wgBXgCVmkox2Eflkk6z4ND3pcwGZ6CfCxTQCDk/dij+2DQ4
 6bmDCy/sBgcbz9mTpoLC11HLoPae6YN9nBNQRZDcEFEu54OaVOqlIdbA6m+POIBCXZdHOFc0
 WoDTgxHRzC1jgQNidyd6tKqcsVJs0dzF0oKTmFFmUAqTdJO12LBuNA1rlqrR3EtpYk8B/wtS
 5dIMD7Q8hwQpL+4C6GNpb6ZKnPkLi47pDOLhz2qBrqN+rqUEsT3YnExYpzj5yOBi+FlmV1Hw
 49QYe1sn2ZPs
In-Reply-To: <CAHgHdKubGHcJiZ6iVoBty5FwF4f+3uE0M00Er4tTsh4_LaH_xw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/4a527da4-7fc3-4c1c-a59b-dd08e405eb38%40dunslane.net>
Precedence: bulk

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


On 2025-03-04 Tu 10:34 AM, Mark Dilger wrote:
>
>
> On Tue, Mar 4, 2025 at 6:05 AM Mark Dilger 
> <mark.dilger@enterprisedb.com> wrote:
>
>     But then I tried:
>
>     +DO $$
>     +DECLARE
>     +  a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"],
>     "aaf": [-6, -8]}'::jsonb;
>     +BEGIN
>     +  WHILE a IS NOT NULL
>     +  LOOP
>     +    RAISE NOTICE '%', a;
>     +    a := COALESCE(a."NU", a[2]);
>     +  END LOOP;
>     +END
>     +$$ LANGUAGE plpgsql;
>     +NOTICE:  {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf":
>     [-6, -8]}
>     +ERROR:  missing FROM-clause entry for table "a"
>     +LINE 1: a := COALESCE(a."NU", a[2])
>     +                      ^
>     +QUERY:  a := COALESCE(a."NU", a[2])
>     +CONTEXT:  PL/pgSQL function inline_code_block line 8 at assignment
>
>     which suggests the plpgsql parser does not recognize a."NU" as
>     we'd expect.  Any thoughts on this?
>
>
> I should mention that
>
> +DO $$
> +DECLARE
> +  a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": 
> [-6, -8]}'::jsonb;
> +BEGIN
> +  WHILE a IS NOT NULL
> +  LOOP
> +    RAISE NOTICE '%', a;
> +    a := COALESCE((a)."NU", (a)[2]);
> +  END LOOP;
> +END
> +$$ LANGUAGE plpgsql;
> +NOTICE:  {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}
> +NOTICE:  [{"": [[3]]}, [6], [2], "bCi"]
> +NOTICE:  [2]
> works fine.  I guess that is good enough.  Should we add these to the 
> sql/jsonb.sql to document the expected behavior, both with the error 
> when using plain "a" and with the correct output when using "(a)"?  
> The reason I mention this is that the plpgsql parser might get changed 
> at some point, and without a test case, we might not notice if this 
> breaks.
>

Yes, I think so.


cheers


andrew


--
Andrew Dunstan
EDB:https://www.enterprisedb.com

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

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 2025-03-04 Tu 10:34 AM, Mark Dilger
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAHgHdKubGHcJiZ6iVoBty5FwF4f+3uE0M00Er4tTsh4_LaH_xw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr"><br>
        </div>
        <br>
        <div class="gmail_quote gmail_quote_container">
          <div dir="ltr" class="gmail_attr">On Tue, Mar 4, 2025 at
            6:05 AM Mark Dilger &lt;<a
              href="mailto:mark.dilger@enterprisedb.com"
              moz-do-not-send="true" class="moz-txt-link-freetext">mark.dilger@enterprisedb.com</a>&gt;
            wrote:<br>
          </div>
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
            <div dir="ltr">But then I tried:<br>
              <div>
                <div dir="ltr">
                  <div><br>
                  </div>
                  <div>+DO $$<br>
                    +DECLARE<br>
                    +  a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2],
                    "bCi"], "aaf": [-6, -8]}'::jsonb;<br>
                    +BEGIN<br>
                    +  WHILE a IS NOT NULL<br>
                    +  LOOP<br>
                    +    RAISE NOTICE '%', a;<br>
                    +    a := COALESCE(a."NU", a[2]);<br>
                    +  END LOOP;<br>
                    +END<br>
                    +$$ LANGUAGE plpgsql;<br>
                    +NOTICE:  {"": 6, "NU": [{"": [[3]]}, [6], [2],
                    "bCi"], "aaf": [-6, -8]}<br>
                    +ERROR:  missing FROM-clause entry for table "a"<br>
                    +LINE 1: a := COALESCE(a."NU", a[2])<br>
                    +                      ^<br>
                    +QUERY:  a := COALESCE(a."NU", a[2])<br>
                    +CONTEXT:  PL/pgSQL function inline_code_block line
                    8 at assignment<br>
                  </div>
                  <div><br>
                  </div>
                  <div>which suggests the plpgsql parser does not
                    recognize a."NU" as we'd expect.  Any thoughts on
                    this?</div>
                </div>
              </div>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div>I should mention that</div>
          <div><br>
          </div>
          <div>+DO $$</div>
          +DECLARE<br>
          +  a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"],
          "aaf": [-6, -8]}'::jsonb;<br>
          +BEGIN<br>
          +  WHILE a IS NOT NULL<br>
          +  LOOP<br>
          +    RAISE NOTICE '%', a;<br>
          +    a := COALESCE((a)."NU", (a)[2]);<br>
          +  END LOOP;<br>
          +END<br>
          +$$ LANGUAGE plpgsql;<br>
          +NOTICE:  {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf":
          [-6, -8]}<br>
          +NOTICE:  [{"": [[3]]}, [6], [2], "bCi"]<br>
          +NOTICE:  [2]<br>
          <div> </div>
          <div>works fine.  I guess that is good enough.  Should we add
            these to the sql/jsonb.sql to document the expected
            behavior, both with the error when using plain "a" and with
            the correct output when using "(a)"?  The reason I mention
            this is that the plpgsql parser might get changed at some
            point, and without a test case, we might not notice if this
            breaks.</div>
          <div><br>
          </div>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>Yes, I think so.</p>
    <p><br>
    </p>
    <p>cheers</p>
    <p><br>
    </p>
    <p>andrew<br>
    </p>
    <p><br>
    </p>
    <pre class="moz-signature"
    signature-switch-id="d0437855-2267-4610-80b3-83167ec45b0b" cols="72">

--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
  </body>
</html>

--------------mJjCjgQgAKQVSfqcp9bI35ec--