Horton
Menu

Re: SQL/JSON: functions

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Oleg Bartunov <obartunov@postgrespro.ru>
Cc: Simon Riggs <simon@2ndquadrant.com>, Nikita Glukhov <n.gluhov@postgrespro.ru>, Andrew Dunstan <andrew.dunstan@2ndquadrant.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Justin Pryzby <pryzby@telsasoft.com>, Alexander Korotkov <a.korotkov@postgrespro.ru>, Erik Rijkers <er@xs4all.nl>, Alvaro Herrera <alvherre@2ndquadrant.com>, Thomas Munro <thomas.munro@gmail.com>, Andrew Alsup <bluesbreaker@gmail.com>, Andres Freund <andres@anarazel.de>, Dmitry Dolgov <9erthalion6@gmail.com>, Michael Paquier <michael@paquier.xyz>
Date: 2020-12-15T19:50:33Z
Lists: pgsql-hackers 
út 15. 12. 2020 v 19:56 odesílatel Oleg Bartunov <obartunov@postgrespro.ru>
napsal:

> On Tue, Dec 15, 2020 at 8:37 PM Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
> >
> >
> >
> > út 15. 12. 2020 v 18:00 odesílatel Simon Riggs <simon@2ndquadrant.com>
> napsal:
> >>
> >> On Fri, 17 Jul 2020 at 21:26, Nikita Glukhov <n.gluhov@postgrespro.ru>
> wrote:
> >> >
> >> > Attached 50th version of the patches. Only the documentation was
> changed
> >> > since the previous version.
> >>
> >> I can imagine the effort required to get to v50, so I salute your
> efforts.
> >>
> >> The document for SQL Standard has now been published as CD
> >> 9075-2-Foundation (Thanks Peter).
> >>
> >> That gives us a clearer picture of what is being voted on and should
> >> allow Nikita to complete his work.
> >>
> >> I suggest we move forwards on this now, but if anyone objects to
> >> including this in PG14 in favour of waiting for the vote, please say
> >> so clearly so we can skip to PG15.
> >
> >
> > Maybe this set of patches can be reorganized and divided. Some parts
> like json generating functions are almost trivial and without
> controversions with clean benefits for users.
>
> I agree with this, most interesting is JSON_TABLE.
>

It is very interesting, but it is very complex too. There is not any
similarly complex function in ANSI SQL. This function defines its own
language.

>
> > The most complexity is related to json_table function. Nikita did a very
> good job and implemented this function in maximal conformance with ANSI SQL
> with a maximal set of features. On second hand it is hard to do review
> because this patch is really complex, and a lot of functionality was not
> implemented elsewhere (so isn't possible to compare results). I think it
> should be possible to reduce complexity and divide acceptance of json_table
> to some steps like basic functionality (on MySQL level), enhanced
> functionality (on Oracle level), and full functionality (the Postgres will
> be first).  This functionality is interesting and maximal conformity with
> SQL/JSON is great so I am for merging it. But if it will be divided into
> some chronological steps, then there can be higher probability of merging
> to upstream in a good time.
>
> I think it's shame to look up on MySQL and Oracle, since we have much
> better and complete implementation of the Standard.
>

Maybe I used bad words. I would not reduce json_table patch to MySQL or
Oracle level. I proposed merging this patch in a few steps when any step
can be functional.

Standard divides JSON supports to some levels too.

There is about 50% code (and features) when review is not a problem,
because this functionality is very clean and natural. Another 50% can be
possibly problematic because Nikita implementation is first in the world
and the description in standard is complex and hard to read, and hard to
test. Because these patches are not divided we have to do lot of repeated
work in every cycle. I proposed to do work more in style step by step than
in big bang style.

I think there is a lot of code that can be commitable immediately (maybe
half). This can be done quickly without any controversies. This reduces
complexity for 50% so we can concentrate better on the rest of patches.
The final target is full support of standard and full merge of Nikita's
patches. Nikita did hard and good work and it is nonsense to throw away any
part of his work - and it is a pity so the merging process is too long
already . But I understand it is pretty hard to commit to this patch in
complexity like this patch has.




>
>
>
> >
> > Regards
> >
> > Pavel
> >
> >>
> >> --
> >> Simon Riggs                http://www.EnterpriseDB.com/
>
>
>
> --
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>

Commits

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

  1. JSON_TABLE: Add support for NESTED paths and columns

  2. Add basic JSON_TABLE() functionality

  3. Add SQL/JSON query functions

  4. Add soft error handling to some expression nodes

  5. Adjust populate_record_field() to handle errors softly

  6. Refactor code used by jsonpath executor to fetch variables

  7. Add more SQL/JSON constructor functions

  8. SQL/JSON: support the IS JSON predicate

  9. SQL/JSON: add standard JSON constructor functions

  10. Improve readability of SQL/JSON documentation.

  11. Documentation for SQL/JSON features

  12. RETURNING clause for JSON() and JSON_SCALAR()

  13. SQL JSON functions

  14. SQL/JSON query functions

  15. IS JSON predicate

  16. SQL/JSON constructors

  17. Common SQL/JSON clauses

  18. Doc: remove obsolete entries in table of BRIN strategy numbers.

  19. Doc: standardize markup a bit more.

  20. Make pg_regress.c unset PGDATABASE during make installcheck.

  21. Fix handling of GENERATED columns in CREATE TABLE LIKE INCLUDING DEFAULTS.