Horton

Re: SQL:2023 JSON simplified accessor support

Alexandra Wang <alexandra.wang.oss@gmail.com>

View thread
From: Alexandra Wang <alexandra.wang.oss@gmail.com>
To: Nikita Malakhov <hukutoc@gmail.com>
Cc: Vik Fearing <vik@postgresfriends.org>, Mark Dilger <mark.dilger@enterprisedb.com>, Matheus Alcantara <matheusssilv97@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, Andrew Dunstan <andrew@dunslane.net>, Nikita Glukhov <glukhov.n.a@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, "David E. Wheeler" <david@justatheory.com>, jian he <jian.universality@gmail.com>
Date: 2025-06-23T14:34:34Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add test coverage for indirection transformation

  2. Fix typo in comment

  3. Implementation of subscripting for jsonb

Hi Nikita,

Thank you so much for reviewing!

On Wed, Apr 23, 2025 at 6:54 PM Nikita Malakhov <hukutoc@gmail.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@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@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@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