Re: SQL:2023 JSON simplified accessor support

This patch set has expanded significantly in scope recently, which is
probably the right thing, but that means there won't be enough time to
review and finish it for PG18. So I'm moving this to the next
commitfest now.

On 13.03.25 15:02, Alexandra Wang wrote:
> Hi Mark,
>
> Thank you so much for reviewing! I have attached the new patches.
>
> On Tue, Mar 4, 2025 at 8:05 AM Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com
> <mailto:mark(dot)dilger(at)enterprisedb(dot)com>> wrote:
>
>
> On Mon, Mar 3, 2025 at 12:23 PM Alexandra Wang
> <alexandra(dot)wang(dot)oss(at)gmail(dot)com <mailto:alexandra(dot)wang(dot)oss(at)gmail(dot)com>>
> wrote:
>
>  I've attached v10, which addresses your feedback.
>
>
> Hi Alex!  Thanks for the patches.
>
> In src/test/regress/sql/jsonb.sql, the section marked with "--
> slices are not supported" should be relabeled.  That comment
> predates these patches, and is now misleading.
>
> A bit further down in expected/jsonb.out, there is an expected
> failure, but no SQL comment to indicate that it is expected:
>
> +SELECT (t.jb).* FROM test_jsonb_dot_notation;
> +ERROR:  missing FROM-clause entry for table "t"
> +LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation;
>
> Perhaps a "-- fails" comment would clarify?  Then, further down,
>
>
> Fixed.
>
> +SELECT (jb).a.**.x FROM test_jsonb_dot_notation; -- not supported
> +ERROR:  syntax error at or near "**"
> +LINE 1: SELECT (jb).a.**.x FROM test_jsonb_dot_notation;
>
> I wonder if it would be better to have the parser handle this case
> and raise a ERRCODE_FEATURE_NOT_SUPPORTED instead?
>
>
> In 0008 I added a new token named "DOUBLE_ASTERISK" to the lexers to
> represent "**". Hope this helps!
>
> I got curious about the support for this new dot notation in the
> plpgsql parser and tried:
>
> +DO $$
> +DECLARE
> +  a jsonb := '[1,2,3,4,5,6,7]'::jsonb;
> +BEGIN
> +  WHILE a IS NOT NULL
> +  LOOP
> +    RAISE NOTICE '%', a;
> +    a := a[2:];
> +  END LOOP;
> +END
> +$$ LANGUAGE plpgsql;
> +NOTICE:  [1, 2, 3, 4, 5, 6, 7]
> +NOTICE:  [3, 4, 5, 6, 7]
> +NOTICE:  [5, 6, 7]
> +NOTICE:  7
>
> which looks good!  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?
>
>
> Thanks for the tests! I added them to the "jsonb" regress test.
>
> I notice there are no changes in src/interfaces/ecpg/test, which
> concerns me.  The sqljson.pgc and sqljson_jsontable.pgc files are
> already testing json handling in ecpg; perhaps just extend those a bit?
>
> Thanks for bringing this up! I have added new tests in src/interfaces/
> ecpg/test/sql/sqljson.pgc.
>
>
> Best,
> Alex