Re: SQL:2023 JSON simplified accessor support

On Tue, Mar 4, 2025 at 6:05 AM Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)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.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company