Re: SQL:2023 JSON simplified accessor support

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