Re: SQL/JSON in PostgreSQL

Hi

2017-02-28 20:08 GMT+01:00 Oleg Bartunov <obartunov(at)gmail(dot)com>:

> Hi there,
>
>
> Attached patch is an implementation of SQL/JSON data model from SQL-2016
> standard (ISO/IEC 9075-2:2016(E)), which was published 2016-12-15 and is
> available only for purchase from ISO web site (
> https://www.iso.org/standard/63556.html) Unfortunately I didn't find any
> public sources of the standard or any preview documents, but Oracle
> implementation of json support in 12c release 2 is very close (
> http://docs.oracle.com/database/122/ADJSN/json-in-oracle-database.htm)
> also we used https://livesql.oracle.com/ to understand some details.
>
> Postgres has already two json data types - json and jsonb and implementing
> another json data type, which strictly conforms the standard, would be not
> a good idea. Moreover, SQL standard doesn’t describe data type, but only
> data model, which “comprises SQL/JSON items and SQL/JSON sequences. The
> components of the SQL/JSON data model are:
>
> 1) An SQL/JSON item is defined recursively as any of the following:
>
> a) An SQL/JSON scalar, defined as a non-null value of any of the following
> predefined (SQL) types:
>
> character string with character set Unicode, numeric, Boolean, or datetime.
>
> b) An SQL/JSON null, defined as a value that is distinct from any value of
> any SQL type.
>
> NOTE 122 — An SQL/JSON null is distinct from the SQL null value.
>
> c) An SQL/JSON array, defined as an ordered list of zero or more SQL/JSON
> items, called the SQL/JSON
>
> elements of the SQL/JSON array.
>
> d) An SQL/JSON object, defined as an unordered collection of zero or more
> SQL/JSON members….
>
> “
>
> Our jsonb corresponds to SQL/JSON with UNIQUE KEYS and implicit ordering
> of keys and our main intention was to provide support of jsonb as a most
> important and usable data type.
>
> We created repository for reviewing (ask for write access) -
> https://github.com/postgrespro/sqljson/tree/sqljson
>
> Examples of usage can be found in src/test/regress/sql/sql_json.sql
>
> The whole documentation about json support should be reorganized and
> added, and we plan to do this before release. We need help of community
> here.
>
> Our goal is to provide support of main features of SQL/JSON to release 10,
> as we discussed at developers meeting in Brussels (Andrew Dunstan has
> kindly agreed to review the patch).
>
> We had not much time to develop the complete support, because of standard
> availability), but hope all major features are here, namely, all nine
> functions as described in the standard (but see implementation notes below):
>
> “All manipulation (e.g., retrieval, creation, testing) of SQL/JSON items
> is performed through a number of SQL/JSON functions. There are nine such
> functions, categorized as SQL/JSON retrieval functions and SQL/JSON
> construction functions. The SQL/JSON retrieval functions are characterized
> by operating on JSON data and returning an SQL value (possibly a Boolean
> value) or a JSON value. The SQL/JSON construction functions return JSON
> data created from operations on SQL data or other JSON data.
>
> The SQL/JSON retrieval functions are:
>
> — <JSON value function>: extracts an SQL value of a predefined type from a
> JSON text.
>
> — <JSON query>: extracts a JSON text from a JSON text.
>
> — <JSON table>: converts a JSON text to an SQL table.
>
> — <JSON predicate>: tests whether a string value is or is not properly
> formed JSON text.
>
> — <JSON exists predicate>: tests whether an SQL/JSON path expression
> returns any SQL/JSON items.
>
> The SQL/JSON construction functions are:
>
> — <JSON object constructor>: generates a string that is a serialization of
> an SQL/JSON object.
>
> — <JSON array constructor>: generates a string that is a serialization of
> an SQL/JSON array.
>
> — <JSON object aggregate constructor>: generates, from an aggregation of
> SQL data, a string that is a serialization
>
> of an SQL/JSON object.
>
> — <JSON array aggregate constructor>: generates, from an aggregation of
> SQL data, a string that is a serialization
>
> of an SQL/JSON array.
>
> A JSON-returning function is an SQL/JSON construction function or
> JSON_QUERY.”
>
> The standard describes SQL/JSON path language, which used by SQL/JSON
> query operators to query JSON. It defines path language as string literal.
> We implemented the path language as JSONPATH data type, since other
> approaches are not friendly to planner and executor.
>
> The functions and JSONPATH provide a new functionality for json support,
> namely, ability to operate (in standard specified way) with json structure
> at SQL-language level - the often requested feature by the users.
>
> The patch is consists of about 15000 insertions (about 5000 lines are from
> tests), passes all regression tests and doesn’t touches critical parts, so
> we hope with community help to bring it to committable state.
>
> Authors: Nikita Glukhov, Teodor Sigaev, Oleg Bartunov and Alexander
> Korotkov
>
> Implementation notes:
>
>
> 1.
>
> We didn’t implemented ‘datetime’ support, since it’s not clear from
> standard.
> 2.
>
> JSON_OBJECT/JSON_OBJECTAGG (KEY <key> VALUE <value>, ...) doesn’t
> implemented, only (<key>:<value>, …) and (<key> VALUE <value>, …) are
> supported, because of grammar conflicts with leading KEY keyword.
> 3.
>
> FORMAT (JSON|JSONB)) in JSON_ARRAYAGG with subquery doesn’t
> supported, because of grammar conflicts with non-reserved word FORMAT.
> 4.
>
> JSONPATH implemented only for jsonb data type , so JSON_EXISTS(),
> JSON_VALUE(), JSON_QUERY() and JSON_TABLE() doesn’t works if context item
> is of json data type.
> 5.
>
> Some methods and predicates for JSONPATH not yet implemented, for
> example .type(), .size(), .keyvalue(), predicates like_regex, starts
> with, etc. They are not key features and we plan to make them in next
> release.
> 6.
>
> JSONPATH doesn’t support expression for index array, like [2+3 to
> $upperbound], only simple constants like [5, 7 to 12] are supported.
> 7.
>
> JSONPATH extensions to standard: .** (wildcard path accessor), .key
> (member accessor without leading @).
> 8.
>
> FORMAT JSONB extension to standard for returning jsonb - standard
> specifies possibility of returning custom type.
> 9.
>
> JSON_EXISTS(), JSON_VALUE(), JSON_QUERY() are implemented using new
> executor node JsonExpr.
> 10.
>
> JSON_TABLE() is transformed into joined subselects with JSON_VALUE()
> and JSON_QUERY() in target list.
> 11.
>
> JSON_OBJECT(), JSON_ARRAY() constructors and IS JSON predicate are
> transformed into raw function calls.
> 12.
>
> Added explicit casts bytea=>jsonb and jsonb=>bytea (for jsonb=>bytea
> output using RETURNING bytea FORMAT JSONB and corresponding bytea=>jsonb
> input using <jsonb_bytea_expr> FORMAT JSONB).
>
>
>
Good work - it will be pretty big patch.

There is a intersection with implementation of XMLTABLE. I prepared a
executor infrastructure. So it can little bit reduce size of this patch.

Taking only Oracle as origin can be risk - in details Oracle doesn't
respects owns proposal to standard.

This is last commitfest for current release cycle - are you sure, so is
good idea to push all mentioned features?

Regards

Pavel

> Best regards,
>
> Oleg
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>