Hi
2017-02-28 20:08 GMT+01:00 Oleg Bartunov <obartu...@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >
