Horton

Re: SQL:2023 JSON simplified accessor support

Andrew Dunstan <andrew@dunslane.net>

View thread
From: Andrew Dunstan <andrew@dunslane.net>
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>
Date: 2025-03-04T18:13:39Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add test coverage for indirection transformation

  2. Fix typo in comment

  3. Implementation of subscripting for jsonb

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