SQL/JSON in PostgreSQL
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).
Best regards,
Oleg
Attachments:
Hi
2017-02-28 20:08 GMT+01:00 Oleg Bartunov <obartunov@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/JSONelements 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/sqljsonExamples 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 serializationof an SQL/JSON object.
— <JSON array aggregate constructor>: generates, from an aggregation of
SQL data, a string that is a serializationof 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
KorotkovImplementation 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
Show quoted text
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
On Tue, Feb 28, 2017 at 10:55 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
Hi
2017-02-28 20:08 GMT+01:00 Oleg Bartunov <obartunov@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/JSONelements 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/sqljsonExamples 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 serializationof an SQL/JSON object.
— <JSON array aggregate constructor>: generates, from an aggregation of
SQL data, a string that is a serializationof 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
KorotkovImplementation 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 :)
Show quoted text
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
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.
It is big, but it is hard to expect so JSON_TABLE can be shorter if you are
solve all commiters requests.
Last patch should be near to final 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 :)
It is not your fault. Ok, I am looking for patches.
Regards
Pavel
Show quoted text
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
1.
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).
This point has sense in Oracle, where JSON is blob. But it is little bit
obscure in PostgreSQL context.
Regards
Pavel
Show quoted text
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
Hi Oleg,
On 2/28/17 2:55 PM, Pavel Stehule wrote:
2017-02-28 20:08 GMT+01:00 Oleg Bartunov <obartunov@gmail.com
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
<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
<http://docs.oracle.com/database/122/ADJSN/json-in-oracle-database.htm>),
also we used https://livesql.oracle.com/ to understand some details.
<...>
This is last commitfest for current release cycle - are you sure, so is
good idea to push all mentioned features?
Implementing standards is always a goal of the PostgreSQL community, but
this is a very large patch arriving very late in the release cycle with
no prior discussion.
That the patch proposed follows a standard which will not be available
to the majority of reviewers is very worrisome, let alone the sheer
size. While much of the code is new, I see many changes to core data
structures that could very easily be destabilizing.
I propose we move this patch to the 2017-07 CF so further development
and review can be done without haste and as the standard becomes more
accessible.
Regards,
--
-David
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2017-03-03 21:49 GMT+01:00 David Steele <david@pgmasters.net>:
Hi Oleg,
On 2/28/17 2:55 PM, Pavel Stehule wrote:
2017-02-28 20:08 GMT+01:00 Oleg Bartunov <obartunov@gmail.com
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
<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
oracle-database.htm>),
also we used https://livesql.oracle.com/ to understand some
details.
<...>
This is last commitfest for current release cycle - are you sure, so is
good idea to push all mentioned features?Implementing standards is always a goal of the PostgreSQL community, but
this is a very large patch arriving very late in the release cycle with
no prior discussion.That the patch proposed follows a standard which will not be available
to the majority of reviewers is very worrisome, let alone the sheer
size. While much of the code is new, I see many changes to core data
structures that could very easily be destabilizing.I propose we move this patch to the 2017-07 CF so further development
and review can be done without haste and as the standard becomes more
accessible.
Although I would to see these features in Postgres early I have same
feeling. Is it a question if some features can be implemented easy and can
be merged early?
The implementation of some JSON generation functions can be easy and the
verification should not be hard. Different situation is in JSON querying
functions. Merging JSONPath in first commitfest is better.
Regards
Pavel
Show quoted text
Regards,
--
-David
david@pgmasters.net
On Fri, Mar 3, 2017 at 11:49 PM, David Steele <david@pgmasters.net> wrote:
Hi Oleg,
On 2/28/17 2:55 PM, Pavel Stehule wrote:
2017-02-28 20:08 GMT+01:00 Oleg Bartunov <obartunov@gmail.com
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
<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
oracle-database.htm>),
also we used https://livesql.oracle.com/ to understand some
details.
<...>
This is last commitfest for current release cycle - are you sure, so is
good idea to push all mentioned features?Implementing standards is always a goal of the PostgreSQL community, but
this is a very large patch arriving very late in the release cycle with
no prior discussion.
We discussed this in Brussels, but I agree, the patch is rather big.
That the patch proposed follows a standard which will not be available
to the majority of reviewers is very worrisome, let alone the sheer
size. While much of the code is new, I see many changes to core data
structures that could very easily be destabilizing.
I don't know when the standard will be publicly available.
I propose we move this patch to the 2017-07 CF so further development
and review can be done without haste and as the standard becomes more
accessible.
I wanted to have one more good feature in 10 and let postgres be on par
with other competitors. SQL/JSON adds many interesting features and users
will be dissapointed if we postpone it for next two years. Let's wait for
reviewers, probably they will find the patch is not very intrusive. We
have a plenty of time and we dedicate one full-time developer for this
project.
Show quoted text
Regards,
--
-David
david@pgmasters.net
Hi,
On 2017-03-07 12:21:59 +0300, Oleg Bartunov wrote:
On 2017-03-03 15:49:38 -0500, David Steele wrote:
I propose we move this patch to the 2017-07 CF so further development
and review can be done without haste and as the standard becomes more
accessible.
+1
I wanted to have one more good feature in 10 and let postgres be on par
with other competitors. SQL/JSON adds many interesting features and users
will be dissapointed if we postpone it for next two years. Let's wait for
reviewers, probably they will find the patch is not very intrusive.
I think it's way too late to late for a patch of this size for 10. And I
don't think it's fair to a lot of other patches of significant size that
have been submitted way earlier, that also need reviewing resources, to
say that we can just see whether it'll get the required resources.
We have a plenty of time and we dedicate one full-time developer for
this project.
How about having that, and perhaps others, developer participate in
reviewing patches and getting to the bottom of the commitfest? Should
we end up being done early, we can look at this patch... There's not
been review activity corresponding to the amount of submissions from
pgpro...
- Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi,
about the datetime issue: as far as I know, JSON does not define a
serialization format for dates and timestamps.
On the other hand, YAML (as a superset of JSON) already supports a
language-independent date(time) serialization format
(http://yaml.org/type/timestamp.html).
I haven't had a glance into the SQL/JSON standard yet and a quick search
didn't reveal anything. However, reading your test case here
https://github.com/postgrespro/sqljson/blob/5a8a241/src/test/regress/sql/sql_json.sql#L411
it seems as if you intend to parse all strings in the form of
"YYYY-MM-DD" as dates. This is problematic in case a string happens to
look like this but is not intended to be a date.
Just for the sake of completeness: YAML solves this issue by omitting
the quotation marks around the date string (just as JSON integers have
no quotations marks around them).
Regards,
Sven
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/7/17 11:38 AM, Andres Freund wrote:
<...>
We have a plenty of time and we dedicate one full-time developer for
this project.How about having that, and perhaps others, developer participate in
reviewing patches and getting to the bottom of the commitfest? Should
we end up being done early, we can look at this patch... There's not
been review activity corresponding to the amount of submissions from
pgpro...
This patch has been moved to CF 2017-07.
--
-David
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Mar 8, 2017 at 12:43 AM, Sven R. Kunze <srkunze@mail.de> wrote:
Hi,
about the datetime issue: as far as I know, JSON does not define a
serialization format for dates and timestamps.On the other hand, YAML (as a superset of JSON) already supports a
language-independent date(time) serialization format (
http://yaml.org/type/timestamp.html).I haven't had a glance into the SQL/JSON standard yet and a quick search
didn't reveal anything. However, reading your test case here
https://github.com/postgrespro/sqljson/blob/5a8a241/src/
test/regress/sql/sql_json.sql#L411 it seems as if you intend to parse all
strings in the form of "YYYY-MM-DD" as dates. This is problematic in case a
string happens to look like this but is not intended to be a date.
SQL/JSON defines methods in jsonpath, in particularly,
| datetime <left paren> [ <JSON datetime template> ] <right paren>
| keyvalue <left paren> <right paren>
<JSON datetime template> ::=
<JSON path string literal>
datetime template is also specified in the standard (very rich)
<datetime template> ::=
{ <datetime template part> }...
<datetime template part> ::=
<datetime template field>
| <datetime template delimiter>
<datetime template field> ::=
<datetime template year>
| <datetime template rounded year>
| <datetime template month>
| <datetime template day of month>
| <datetime template day of year>
| <datetime template 12-hour>
| <datetime template 24-hour>
| <datetime template minute>
| <datetime template second of minute>
| <datetime template second of day>
| <datetime template fraction>
| <datetime template am/pm>
| <datetime template time zone hour>
| <datetime template time zone minute>
<datetime template delimiter> ::=
<minus sign>
| <period>
| <solidus>
| <comma>
| <apostrophe>
| <semicolon>
| <colon>
| <space>
<datetime template year> ::=
YYYY | YYY | YY | Y
<datetime template rounded year> ::=
RRRR | RR
<datetime template month> ::=
MM
<datetime template day of month> ::=
DD
<datetime template day of year> ::=
DDD
<datetime template 12-hour> ::=
HH | HH12
<datetime template 24-hour> ::=
HH24
<datetime template minute> ::=
MI
<datetime template second of minute> ::=
SS
<datetime template second of day> ::=
SSSSS
<datetime template fraction> ::=
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
<datetime template am/pm> ::=
A.M. | P.M.
<datetime template time zone hour> ::=
TZH
<datetime template time zone minute> ::=
TZM
Just for the sake of completeness: YAML solves this issue by omitting the
quotation marks around the date string (just as JSON integers have no
quotations marks around them).
interesting idea, but need to dig the standard first.
Show quoted text
Regards,
Sven
On Wed, Mar 8, 2017 at 7:05 AM, David Steele <david@pgmasters.net> wrote:
On 3/7/17 11:38 AM, Andres Freund wrote:
<...>
We have a plenty of time and we dedicate one full-time developer for
this project.
How about having that, and perhaps others, developer participate in
reviewing patches and getting to the bottom of the commitfest? Should
we end up being done early, we can look at this patch... There's not
been review activity corresponding to the amount of submissions from
pgpro...This patch has been moved to CF 2017-07.
Yes, after committing XMLTABLE, we anyway need to extend its infrastructure
to support JSON_TABLE.
Show quoted text
--
-David
david@pgmasters.net
Small point of order: YAML is not strictly a super-set of JSON.
Editorializing slightly, I have not seen much interest in the world for
YAML support though I'd be interested in evidence to the contrary.
On Tue, Mar 7, 2017 at 1:43 PM, Sven R. Kunze <srkunze@mail.de> wrote:
Hi,
about the datetime issue: as far as I know, JSON does not define a
serialization format for dates and timestamps.On the other hand, YAML (as a superset of JSON) already supports a
language-independent date(time) serialization format (
http://yaml.org/type/timestamp.html).I haven't had a glance into the SQL/JSON standard yet and a quick search
didn't reveal anything. However, reading your test case here
https://github.com/postgrespro/sqljson/blob/5a8a241/src/
test/regress/sql/sql_json.sql#L411 it seems as if you intend to parse all
strings in the form of "YYYY-MM-DD" as dates. This is problematic in case a
string happens to look like this but is not intended to be a date.Just for the sake of completeness: YAML solves this issue by omitting the
quotation marks around the date string (just as JSON integers have no
quotations marks around them).Regards,
Sven--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut
On Wed, Mar 8, 2017 at 11:48 AM, Peter van Hardenberg <pvh@pvh.ca> wrote:
Small point of order: YAML is not strictly a super-set of JSON.
Editorializing slightly, I have not seen much interest in the world for
YAML support though I'd be interested in evidence to the contrary.
The world of configuration management seems to for some reason run off
YAML, but that's the only places I've seen it recently (ansible, puppet
etc).
That said if we're introducing something new, it's usually better to copy
from another format than to invite your own.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Tue, Mar 7, 2017 at 2:38 PM, Andres Freund <andres@anarazel.de> wrote:
On 2017-03-07 12:21:59 +0300, Oleg Bartunov wrote:
On 2017-03-03 15:49:38 -0500, David Steele wrote:
I propose we move this patch to the 2017-07 CF so further development
and review can be done without haste and as the standard becomes more
accessible.+1
I agree that this should not go into v10. February 28th is not the
right time for a large, never-before-seen patch to show up with
expectations of getting committed for the current cycle.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08.03.2017 20:48, Peter van Hardenberg wrote:
Small point of order: YAML is not strictly a super-set of JSON.
I haven't read the whole standard, but from what I can see the standard
considers JSON an official subset of itself:
http://www.yaml.org/spec/1.2/spec.html
Regards,
Sven
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 9, 2017 at 12:48 PM, Sven R. Kunze <srkunze@mail.de> wrote:
On 08.03.2017 20:48, Peter van Hardenberg wrote:
Small point of order: YAML is not strictly a super-set of JSON.
I haven't read the whole standard, but from what I can see the standard
considers JSON an official subset of itself:
http://www.yaml.org/spec/1.2/spec.html
But there's apparent sophistry, like this, in that spec:
SON's RFC4627 requires that mappings keys merely “SHOULD” be unique,
while YAML insists they “MUST” be. Technically, YAML therefore
complies with the JSON spec, choosing to treat duplicates as an error.
In practice, since JSON is silent on the semantics of such duplicates,
the only portable JSON files are those with unique keys, which are
therefore valid YAML files.
I don't see how YAML can impose a stronger requirement than JSON and
yet claim to be a superset; a JSON document that doesn't meet that
requirement will be legal (if stupid) as JSON but illegal as YAML.
Also, even if the superset thing were true on a theoretical plane, I'm
not sure it would do us much good in practice. If we start using
YAML-specific constructs, we won't have valid JSON any more. If we
use only things that are legal in JSON, YAML's irrelevant.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08.03.2017 20:52, Magnus Hagander wrote:
On Wed, Mar 8, 2017 at 11:48 AM, Peter van Hardenberg <pvh@pvh.ca
<mailto:pvh@pvh.ca>> wrote:Small point of order: YAML is not strictly a super-set of JSON.
Editorializing slightly, I have not seen much interest in the
world for YAML support though I'd be interested in evidence to the
contrary.The world of configuration management seems to for some reason run off
YAML, but that's the only places I've seen it recently (ansible,
puppet etc).
SaltStack uses YAML for their tools, too. I personally can empathize
with them (as a user of configuration management) about this as writing
JSON would be nightmare with all the quoting, commas, curly braces etc.
But that's my own preference maybe.
(Btw. does "run off" mean like or avoid? At least my dictionaries tend
to the latter.)
That said if we're introducing something new, it's usually better to
copy from another format than to invite your own.
From my day-to-day work I can tell, the date(time) type is the only
missing piece of JSON to make it perfect for business applications
(besides, maybe, a "currency" type).
Regards,
Sven
On 09.03.2017 18:58, Robert Haas wrote:
Also, even if the superset thing were true on a theoretical plane, I'm
not sure it would do us much good in practice. If we start using
YAML-specific constructs, we won't have valid JSON any more. If we
use only things that are legal in JSON, YAML's irrelevant.
That's true. I just wanted to share my view of the "date guessing" part
of pgpro's commits.
I don't have a good solution for it either, I can only tell that where I
work we do have same issues: either we guess by looking at the string
value or we know that "this particular key" must be a date.
Unsatisfied with either solution, we tend to use YAML for our APIs if
possible.
Regards,
Sven
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Anecdotally, we just stored dates as strings and used a convention (key
ends in "_at", I believe) to interpret them. The lack of support for dates
in JSON is well-known, universally decried... and not a problem the
PostgreSQL community can fix.
On Thu, Mar 9, 2017 at 10:24 AM, Sven R. Kunze <srkunze@mail.de> wrote:
On 09.03.2017 18:58, Robert Haas wrote:
Also, even if the superset thing were true on a theoretical plane, I'm
not sure it would do us much good in practice. If we start using
YAML-specific constructs, we won't have valid JSON any more. If we
use only things that are legal in JSON, YAML's irrelevant.That's true. I just wanted to share my view of the "date guessing" part of
pgpro's commits.
I don't have a good solution for it either, I can only tell that where I
work we do have same issues: either we guess by looking at the string value
or we know that "this particular key" must be a date.
Unsatisfied with either solution, we tend to use YAML for our APIs if
possible.Regards,
Sven--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Peter van Hardenberg
San Francisco, California
"Everything was beautiful, and nothing hurt."—Kurt Vonnegut
On 09.03.2017 19:50, Peter van Hardenberg wrote:
Anecdotally, we just stored dates as strings and used a convention
(key ends in "_at", I believe) to interpret them. The lack of support
for dates in JSON is well-known, universally decried... and not a
problem the PostgreSQL community can fix.
I completely agree here.
Regards,
Sven
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 09/03/17 19:50, Peter van Hardenberg wrote:
Anecdotally, we just stored dates as strings and used a convention (key
ends in "_at", I believe) to interpret them. The lack of support for
dates in JSON is well-known, universally decried... and not a problem
the PostgreSQL community can fix.
The original complain was about JSON_VALUE extracting date but I don't
understand why there is problem with that, the SQL/JSON defines that
behavior. The RETURNING clause there is more or less just shorthand for
casting with some advanced options.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10.03.2017 05:07, Petr Jelinek wrote:
The original complain was about JSON_VALUE extracting date but I don't
understand why there is problem with that, the SQL/JSON defines that
behavior. The RETURNING clause there is more or less just shorthand for
casting with some advanced options.
Thanks for clarifying. I mistook it as if JSON_VALUE itself returns a
date value.
Sven
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 9, 2017 at 1:12 PM, Sven R. Kunze <srkunze@mail.de> wrote:
On 08.03.2017 20:52, Magnus Hagander wrote:
On Wed, Mar 8, 2017 at 11:48 AM, Peter van Hardenberg <pvh@pvh.ca> wrote:
Small point of order: YAML is not strictly a super-set of JSON.
Editorializing slightly, I have not seen much interest in the world for
YAML support though I'd be interested in evidence to the contrary.The world of configuration management seems to for some reason run off
YAML, but that's the only places I've seen it recently (ansible, puppet
etc).SaltStack uses YAML for their tools, too. I personally can empathize with
them (as a user of configuration management) about this as writing JSON
would be nightmare with all the quoting, commas, curly braces etc. But
that's my own preference maybe.(Btw. does "run off" mean like or avoid? At least my dictionaries tend to
the latter.)
In this case, it means like. "run off" as in "the car runs off fuel" or
something like that. Probably a bad choice of words.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On 03/09/2017 10:12 AM, Sven R. Kunze wrote:
On 08.03.2017 20:52, Magnus Hagander wrote:
On Wed, Mar 8, 2017 at 11:48 AM, Peter van Hardenberg <pvh@pvh.ca
<mailto:pvh@pvh.ca>> wrote:Small point of order: YAML is not strictly a super-set of JSON.
Editorializing slightly, I have not seen much interest in the
world for YAML support though I'd be interested in evidence to the
contrary.The world of configuration management seems to for some reason run off
YAML, but that's the only places I've seen it recently (ansible,
puppet etc).SaltStack uses YAML for their tools, too. I personally can empathize
with them (as a user of configuration management) about this as writing
JSON would be nightmare with all the quoting, commas, curly braces etc.
But that's my own preference maybe.(Btw. does "run off" mean like or avoid? At least my dictionaries tend
to the latter.)
Yes, but automated tools can easily convert between JSON and
newline-delimited YAML and back.
--
Josh Berkus
Containers & Databases Oh My!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 10, 2017 at 7:07 AM, Petr Jelinek <petr.jelinek@2ndquadrant.com>
wrote:
On 09/03/17 19:50, Peter van Hardenberg wrote:
Anecdotally, we just stored dates as strings and used a convention (key
ends in "_at", I believe) to interpret them. The lack of support for
dates in JSON is well-known, universally decried... and not a problem
the PostgreSQL community can fix.The original complain was about JSON_VALUE extracting date but I don't
understand why there is problem with that, the SQL/JSON defines that
behavior. The RETURNING clause there is more or less just shorthand for
casting with some advanced options.
There is no problem with serializing date and SQL/JSON describes it rather
well. There is no correct procedure to deserialize date from a correct json
string and the standards keeps silence about this and now we understand
that date[time] is actually virtual and the only use of them is in jsonpath
(filter) expressions.
Show quoted text
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Mar 07, 2017 at 10:43:16PM +0100, Sven R. Kunze wrote:
about the datetime issue: as far as I know, JSON does not define a
serialization format for dates and timestamps.
Use strings in ISO 8601 format, with or without fractional seconds, and
maybe with 5-digit years.
On the other hand, YAML (as a superset of JSON) already supports a
language-independent date(time) serialization format
(http://yaml.org/type/timestamp.html).
But YAML isn't what this is about.
Nico
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 09, 2017 at 12:58:55PM -0500, Robert Haas wrote:
On Thu, Mar 9, 2017 at 12:48 PM, Sven R. Kunze <srkunze@mail.de> wrote:
On 08.03.2017 20:48, Peter van Hardenberg wrote:
Small point of order: YAML is not strictly a super-set of JSON.
I haven't read the whole standard, but from what I can see the standard
considers JSON an official subset of itself:
http://www.yaml.org/spec/1.2/spec.htmlBut there's apparent sophistry, like this, in that spec:
I agree with you. But beware, the IETF has had multiple threads with
thousands of posts in them about these sorts of issues. If you're not
careful you'll have such a thread on this list too. It would be very
sad not to only let a group that really cares have such threads instead.
:)
Nico
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 09, 2017 at 07:12:07PM +0100, Sven R. Kunze wrote:
From my day-to-day work I can tell, the date(time) type is the only missing
piece of JSON to make it perfect for business applications (besides, maybe,
a "currency" type).
And a binary type. And a chunked-string type (to avoid having to escape
strings). And an interval type. And...
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2017-03-13 7:24 GMT+01:00 Nico Williams <nico@cryptonector.com>:
On Thu, Mar 09, 2017 at 07:12:07PM +0100, Sven R. Kunze wrote:
From my day-to-day work I can tell, the date(time) type is the only
missing
piece of JSON to make it perfect for business applications (besides,
maybe,
a "currency" type).
And a binary type. And a chunked-string type (to avoid having to escape
strings). And an interval type. And...
It is designed/born be simple - if you need some more complex, then you can
use XML with schema, ...
Pavel
On Mon, Mar 13, 2017 at 9:24 AM, Nico Williams <nico@cryptonector.com>
wrote:
On Thu, Mar 09, 2017 at 07:12:07PM +0100, Sven R. Kunze wrote:
From my day-to-day work I can tell, the date(time) type is the only
missing
piece of JSON to make it perfect for business applications (besides,
maybe,
a "currency" type).
And a binary type. And a chunked-string type (to avoid having to escape
strings). And an interval type. And...
Let's first have this basic implementation in postgres, then we'll add
extendability support not only for types, but also for operators.
Right now I see in our regression tests:
select _jsonpath_object(
'["10.03.2017 12:34 +1", "10.03.2017 12:35 +1", "10.03.2017 12:36 +1",
"10.03.2017 12:35 +2", "10.03.2017 12:35 -2"]',
'$[*].datetime("dd.mm.yyyy HH24:MI TZH") ? (@ < "10.03.2017 12:35
+1".datetime("dd.mm.yyyy HH24:MI TZH"))'
);
_jsonpath_object
--------------------------
"2017-03-10 14:34:00+03"
"2017-03-10 13:35:00+03"
(2 rows)
On 10.03.2017 20:28, Josh Berkus wrote:
On 03/09/2017 10:12 AM, Sven R. Kunze wrote:
SaltStack uses YAML for their tools, too. I personally can empathize
with them (as a user of configuration management) about this as writing
JSON would be nightmare with all the quoting, commas, curly braces etc.
But that's my own preference maybe.Yes, but automated tools can easily convert between JSON and
newline-delimited YAML and back.
Sure. That wasn't point, though.
Sven
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 13.03.2017 07:24, Nico Williams wrote:
On Thu, Mar 09, 2017 at 07:12:07PM +0100, Sven R. Kunze wrote:
From my day-to-day work I can tell, the date(time) type is the only missing
piece of JSON to make it perfect for business applications (besides, maybe,
a "currency" type).And a binary type. And a chunked-string type (to avoid having to escape
strings). And an interval type. And...
YMMV but I tend to say that those aren't the usual types of a business
application where I come from.
Answering questions like "how many" (integer), "what" (text) and "when"
(date) is far more common than "give me that binary blob" at least in
the domain where I work. Never had the necessity for an interval type;
usually had a start and end value where the "interval" was derived from
those values.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/7/17 11:05 PM, David Steele wrote:
On 3/7/17 11:38 AM, Andres Freund wrote:
<...>
We have a plenty of time and we dedicate one full-time developer for
this project.How about having that, and perhaps others, developer participate in
reviewing patches and getting to the bottom of the commitfest? Should
we end up being done early, we can look at this patch... There's not
been review activity corresponding to the amount of submissions from
pgpro...This patch has been moved to CF 2017-07.
I did not manage to move this patch when I said had. It is now moved.
Thank,
--
-David
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/15/17 11:56, David Steele wrote:
This patch has been moved to CF 2017-07.
I did not manage to move this patch when I said had. It is now moved.
Unsurprisingly, this patch needs a major rebase.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 Aug 2017, at 04:30, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
On 3/15/17 11:56, David Steele wrote:
This patch has been moved to CF 2017-07.
I did not manage to move this patch when I said had. It is now moved.
Unsurprisingly, this patch needs a major rebase.
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.
cheers ./daniel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson <daniel@yesql.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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson <daniel@yesql.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
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).
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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15.09.2017 22:36, Oleg Bartunov wrote:
On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson <daniel@yesql.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_v10Since 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).
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_wip
https://github.com/postgrespro/sqljson/tree/sqljson_v10
https://github.com/postgrespro/sqljson/tree/sqljson_v10_wip
Attached patches can be produced simply by combining groups of consecutive
commits from these branches.
--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
Attachments:
sqljson_v02.tgzapplication/x-compressed-tar; name=sqljson_v02.tgzDownload
� W�Y �<�[9����WhI&����$;0��`��L������e�CNw;�������m0$����{��VKU%�T�$O�V�W��{��W���F
����S�O���g}w����z�Q��Po�wvk;�f��C��l5�?��w�����U��>�p�!����C?�1��Jef�L��\m{�j������k>5L�&K�����[����n[�VUM���V��n���Je�|�TZE���Y���������
�w\u��Mk�n�7��]������~�m�m��>����f�@�-|�����j�.����@��)`�������Y��"��v��|I����(�2sU+�)�t�/e�"KQ�����8g�vn�������U��n�JIb
��������u��C
;������.0}�d�bb���]qK����U�O���,��^����]+��}���;se�0L]7�"-�ak�B��r�bRBj����l;�#��j�%�A��8���z���������;�t
��������}�x;1w��?��k:�|Y�"{�Sq������m{a���+6�������2����u��:j��g�� �b,6��a4��k��z�_V�!����@�(���/�mG�?7X��������?�{�'M����J�*w
����� �����4{�����C���������s��,8�Q��i�
f^�;[���5x
�D��K\���P�}udcz�����{�\R`�����q���
2��i��B���\�9��������(bv����n�w�!�1�y��\����B����_~!�;�?s�����-�����Q�B���i�s��w���*���,�
��,w��e���f�E��i �Z�a�����`.�5IG��R�{�%���M(��H��/�M(�i!a����i@ ���=3���Nl���T����w��zM��wk�c��l��!��[n��G�%��T�� �>