Re: SQL/JSON in PostgreSQL

On Tue, Feb 28, 2017 at 10:55 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 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.
>

we considered your XMLTABLE patch, but it's itself pretty big and in
unknown state.

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

we used an original standard document ! I suggest Oracle to those, who
don't have access to standard. Yes, there are some problem in Oracle's
implementation.

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

This would be a great feature for Release 10 and I understand all risks.
Hopefully, community will help us. We have resources to continue our work
and will do as much as possible to satisfy community requirements. It's not
our fault, that standard was released so late :)

>
> 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
>>
>>
>