Re: SQL:2023 JSON simplified accessor support

Hi Nikita,

Thank you so much for reviewing!

On Wed, Apr 23, 2025 at 6:54 PM Nikita Malakhov <hukutoc(at)gmail(dot)com> wrote:

> Hi Alex!
>
> Glad you made so much effort to develop this patch set!
> I think this is an important part of Json functionality.
>
> I've looked into you patch and noticed change in behavior
> in new test results:
>
> postgres(at)postgres=# create table t(x int, y jsonb);
> insert into t select 1, '{"a": 1, "b": 42}'::jsonb;
> insert into t select 1, '{"a": 2, "b": {"c": 42}}'::jsonb;
> insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::jsonb;
> CREATE TABLE
> Time: 6.373 ms
> INSERT 0 1
> Time: 3.299 ms
> INSERT 0 1
> Time: 2.532 ms
> INSERT 0 1
> Time: 2.453 ms
>
> Original master:
> postgres(at)postgres=# select (t.y).b.c.d.e from t;
> ERROR: column notation .b applied to type jsonb, which is not a composite
> type
> LINE 1: select (t.y).b.c.d.e from t;
> ^
> Time: 0.553 ms
>
> Patched (with v11):
> postgres(at)postgres=# select (t.y).b.c.d.e from t;
> e
> ---
>
>
>
> (3 rows)
>
> Is this correct?
>

This is correct.

With this patch, the query should return 3 empty rows. We expect
dot notation to behave the same as the json_query() below in lax mode
with NULL ON EMPTY.

postgres=# select json_query(y, 'lax $.b.c.d.e' WITH CONDITIONAL ARRAY
WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
json_query
------------

(3 rows)

Best,
Alex