Re: SQL/JSON in PostgreSQL

On 29.09.2017 12:59, Pavel Stehule wrote:

> Hi
>
> 2017-09-16 1:31 GMT+02:00 Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru
> <mailto:n(dot)gluhov(at)postgrespro(dot)ru>>:
>
> 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 <mailto:robertmhaas(at)gmail(dot)com>> wrote:
>
> On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson
> <daniel(at)yesql(dot)se <mailto: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
> https://github.com/postgrespro/sqljson/tree/sqljson_v10
> <https://github.com/postgrespro/sqljson/tree/sqljson_v10>
>
> Since the first post we made a lot of changes, mostly because of
> better understanding the standard and availability of
> technical report
> (http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
> <http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip>).
> 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.
>
> 0001-jsonpath-v02.patch:
>  - jsonpath type
>  - jsonpath execution on jsonb type
>  - jsonpath operators for jsonb type
>  - GIN support for jsonpath operators
>
> 0002-jsonpath-json-v02.patch:
>  - jsonb-like iterators for json type
>  - jsonpath execution on json type
>  - jsonpath operators for json type
>
> 0003-jsonpath-extensions-v02.patch:
> 0004-jsonpath-extensions-tests-for-json-v02.patch:
>  - some useful standard extensions with tests
>  0005-sqljson-v02.patch:
>  - SQL/JSON constructors (JSON_OBJECT[AGG], JSON_ARRAY[AGG])
>  - SQL/JSON query functions (JSON_VALUE, JSON_QUERY, JSON_EXISTS)
>  - IS JSON predicate
>
> 0006-sqljson-json-v02.patch:
>  - SQL/JSON support for json type and tests
>
> 0007-json_table-v02.patch:
>  - JSON_TABLE using XMLTABLE infrastructure
>
> 0008-json_table-json-v02.patch:
>  - JSON_TABLE support for json type
>
> 0009-wip-extensions-v02.patch:
>  - FORMAT JSONB
>  - 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:
>
> https://github.com/postgrespro/sqljson/tree/sqljson
> <https://github.com/postgrespro/sqljson/tree/sqljson>
> https://github.com/postgrespro/sqljson/tree/sqljson_wip
> <https://github.com/postgrespro/sqljson/tree/sqljson_wip>
> https://github.com/postgrespro/sqljson/tree/sqljson_v10
> <https://github.com/postgrespro/sqljson/tree/sqljson_v10>
> https://github.com/postgrespro/sqljson/tree/sqljson_v10_wip
> <https://github.com/postgrespro/sqljson/tree/sqljson_v10_wip>
>
> Attached patches can be produced simply by combining groups of
> consecutive
> commits from these branches.
>
>
> I have some free time now. Is it last version?
>
> Regards
>
> Pavel
>
Yes, this is still the latest version. Now I am working only on
unfinished WIP
patch no. 9, but I think it should be reviewed the last.

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company