Re: SQL:2023 JSON simplified accessor support

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.dilger@enterprisedb.com>
wrote:

>
> On Mon, Mar 3, 2025 at 12:23 PM Alexandra Wang <
> alexandra.wang.oss@gmail.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