Re: SQL/JSON in PostgreSQL

From: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
To: obartunov(at)gmail(dot)com, Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Daniel Gustafsson <daniel(at)yesql(dot)se>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, Teodor Sigaev <teodor(at)postgrespro(dot)ru>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, David Steele <david(at)pgmasters(dot)net>, Andres Freund <andres(at)anarazel(dot)de>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>, andrew Dunstan <andrew(at)dunslane(dot)net>
Subject: Re: SQL/JSON in PostgreSQL
Date: 2017-09-15 23:31:43
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 15.09.2017 22:36, Oleg Bartunov wrote:

> On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:
>>> Can we expect a rebased version of this patch for this commitfest? Since it’s
>>> a rather large feature it would be good to get it in as early as we can in the
>>> process.
>> Again, given that this needs a "major" rebase and hasn't been updated
>> in a month, and given that the CF is already half over, this should
>> just be bumped to the next CF. We're supposed to be trying to review
>> things that were ready to go by the start of the CF, not the end.
> We are supporting v10 branch in our github repository
> Since the first post we made a lot of changes, mostly because of
> better understanding the standard and availability of technical report
> (
> Most important are:
> 1.We abandoned FORMAT support, which could confuse our users, since we
> have data types json[b].
> 2. We use XMLTABLE infrastructure, extended for JSON_TABLE support.
> 3. Reorganize commits, so we could split one big patch by several
> smaller patches, which could be reviewed independently.
> 4. The biggest problem is documentation, we are working on it.
> Nikita will submit patches soon.

Attached archive with 9 patches rebased onto latest master.

- jsonpath type
- jsonpath execution on jsonb type
- jsonpath operators for jsonb type
- GIN support for jsonpath operators

- jsonb-like iterators for json type
- jsonpath execution on json type
- jsonpath operators for json type

- some useful standard extensions with tests

- IS JSON predicate

- SQL/JSON support for json type and tests

- JSON_TABLE using XMLTABLE infrastructure

- JSON_TABLE support for json type

- jsonb to/from bytea casts
- jsonpath operators
- some unfinished jsonpath extensions

Originally, JSON path was implemented only for jsonb type, and I decided to
add jsonb-like iterators for json type for json support implementation with
minimal changes in JSON path code. This solution (see jsonpath_json.c from
patch 0002) looks a little dubious to me, so I separated json support into
independent patches.

The last WIP patch 0009 is unfinished and contains a lot of FIXMEs. But
the ability to use arbitrary Postgres operators in JSON path with explicitly
specified types is rather interesting, and I think it should be shown now
to get a some kind of pre-review.

We are supporting v11 and v10 branches in our github repository:

Attached patches can be produced simply by combining groups of consecutive
commits from these branches.

Nikita Glukhov
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
sqljson_v02.tgz application/x-compressed-tar 202.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2017-09-15 23:46:05 Re: [PATCH] Incremental sort
Previous Message Amit Langote 2017-09-15 23:07:22 Re: path toward faster partition pruning