Re: [HACKERS] SQL/JSON in PostgreSQL

[Michael Paquier]

On Fri, Nov 3, 2017 at 12:07 PM, Michael Paquier
 wrote:
> The patch sent previously does not directly apply on HEAD, and as far
> as I can see the last patch set published on
> https://www.postgresql.org/message-id/2361ae4a-66b1-c6c5-ea6a-84851a1c0...@postgrespro.ru
> has rotten. Could you send a new patch set?
>
> About the patch set, I had a look at the first patch which is not that
> heavy, however it provides zero documentation, close to zero comments,
> but adds more than 500 lines of code. I find that a bit hard to give
> an opinion on, having commit messages associated to each patch would
> be also nice. This way, reviewers can figure what's going out in this
> mess and provide feedback. Making things incremental is welcome as
> well, for example in the first patch I have a hard way finding out why
> timestamps are touched to begin with.

My mistake here, only the first patch adds 8,200 lines of code. This
makes the lack of comments and docs even worse.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL/JSON in PostgreSQL

[Michael Paquier]

On Fri, Nov 3, 2017 at 11:29 AM, Nikita Glukhov  wrote:
> By standard only string literals can be used in JSON path specifications.
> But of course it is possible to allow to use variable jsonpath expressions
> in
> SQL/JSON functions.
>
> Attached patch implements this feature for JSON query functions, JSON_TABLE
> is
> not supported now because it needs some refactoring.
>
> I have pushed this commit to the separate branch because it is not finished
> yet:
> https://github.com/postgrespro/sqljson/tree/sqljson_variable_json_path

The patch sent previously does not directly apply on HEAD, and as far
as I can see the last patch set published on
https://www.postgresql.org/message-id/2361ae4a-66b1-c6c5-ea6a-84851a1c0...@postgrespro.ru
has rotten. Could you send a new patch set?

About the patch set, I had a look at the first patch which is not that
heavy, however it provides zero documentation, close to zero comments,
but adds more than 500 lines of code. I find that a bit hard to give
an opinion on, having commit messages associated to each patch would
be also nice. This way, reviewers can figure what's going out in this
mess and provide feedback. Making things incremental is welcome as
well, for example in the first patch I have a hard way finding out why
timestamps are touched to begin with.

The patch is already marked as "waiting on author" for more than one month.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL/JSON in PostgreSQL

[Nikita Glukhov]

On 03.11.2017 00:32, Piotr Stefaniak wrote:


On 2017-02-28 20:08, Oleg Bartunov wrote:

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.

I was a bit sad to discover that I can't
PREPARE jsq AS SELECT JSON_QUERY('{}', $1);
I assume because of this part of the updated grammar:
json_path_specification:
 Sconst { $$ = $1; }
;

Would it make sense, fundamentally, to allow variables there? After
Andrew Gierth's analysis of this grammar problem, I understand that it's
not reasonable to expect JSON_TABLE() to support variable jsonpaths, but
maybe it would be feasible for everything else? From Andrew's changes to
the new grammar (see attached) it seems to me that at least that part is
possible. Or should I forget about trying to implement the other part?

By standard only string literals can be used in JSON path specifications.
But of course it is possible to allow to use variable jsonpath 
expressions in

SQL/JSON functions.

Attached patch implements this feature for JSON query functions, 
JSON_TABLE is

not supported now because it needs some refactoring.

I have pushed this commit to the separate branch because it is not 
finished yet:

https://github.com/postgrespro/sqljson/tree/sqljson_variable_json_path

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
>From cba58ec1410eb99fc974d8a46013ce7331c93377 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov 
Date: Fri, 3 Nov 2017 14:15:51 +0300
Subject: [PATCH] Allow variable jsonpath specifications

---
 src/backend/executor/execExpr.c   |  7 +++
 src/backend/executor/execExprInterp.c |  5 ++---
 src/backend/nodes/copyfuncs.c |  2 +-
 src/backend/parser/gram.y | 11 ++-
 src/backend/parser/parse_clause.c | 35 ++-
 src/backend/parser/parse_expr.c   | 19 +--
 src/backend/utils/adt/ruleutils.c | 11 +--
 src/include/executor/execExpr.h   |  3 ++-
 src/include/nodes/parsenodes.h|  2 +-
 src/include/nodes/primnodes.h |  2 +-
 10 files changed, 72 insertions(+), 25 deletions(-)

diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 3cc8e12..86030b9 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -2042,6 +2042,13 @@ ExecInitExprRec(Expr *node, PlanState *parent, ExprState *state,
 _expr->value,
 _expr->isnull);
 
+scratch.d.jsonexpr.pathspec =
+	palloc(sizeof(*scratch.d.jsonexpr.pathspec));
+
+ExecInitExprRec((Expr *) jexpr->path_spec, parent, state,
+>value,
+>isnull);
+
 scratch.d.jsonexpr.formatted_expr =
 		ExecInitExpr((Expr *) jexpr->formatted_expr, parent);
 
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index b9d719d..36ef0fd 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -3897,7 +3897,7 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
 	*op->resnull = true;		/* until we get a result */
 	*op->resvalue = (Datum) 0;
 
-	if (op->d.jsonexpr.raw_expr->isnull)
+	if (op->d.jsonexpr.raw_expr->isnull || op->d.jsonexpr.pathspec->isnull)
 	{
 		/* execute domain checks for NULLs */
 		(void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull, isjsonb);
@@ -3905,8 +3905,7 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
 	}
 
 	item = op->d.jsonexpr.raw_expr->value;
-
-	path = DatumGetJsonPath(jexpr->path_spec->constvalue);
+	path = DatumGetJsonPath(op->d.jsonexpr.pathspec->value);
 
 	/* reset JSON path variable contexts */
 	foreach(lc, op->d.jsonexpr.args)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 80c0e48..34fed1d 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2332,7 +2332,7 @@ _copyJsonCommon(const JsonCommon *from)
 	JsonCommon	   *newnode = makeNode(JsonCommon);
 
 	COPY_NODE_FIELD(expr);
-	COPY_STRING_FIELD(pathspec);
+	COPY_NODE_FIELD(pathspec);
 	COPY_STRING_FIELD(pathname);
 	COPY_NODE_FIELD(passing);
 	COPY_LOCATION_FIELD(location);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index adfe9b1..71a59d1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -624,6 +624,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	json_table_plan_cross
 	json_table_plan_primary
 	json_table_default_plan
+	json_path_specification
 
 %type 		json_arguments
 	json_passing_clause_opt
@@ -635,8 +636,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type 		json_returning_clause_opt
 

Re: [HACKERS] SQL/JSON in PostgreSQL

[Piotr Stefaniak]

On 2017-02-28 20:08, Oleg Bartunov wrote:
> Attached patch is an implementation of SQL/JSON data model from SQL-2016
> standard (ISO/IEC 9075-2:2016(E))

I've faintly started looking into this.

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


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

I was a bit sad to discover that I can't
PREPARE jsq AS SELECT JSON_QUERY('{}', $1);
I assume because of this part of the updated grammar:
json_path_specification:
Sconst { $$ = $1; }
   ;

Would it make sense, fundamentally, to allow variables there? After 
Andrew Gierth's analysis of this grammar problem, I understand that it's 
not reasonable to expect JSON_TABLE() to support variable jsonpaths, but 
maybe it would be feasible for everything else? From Andrew's changes to 
the new grammar (see attached) it seems to me that at least that part is 
possible. Or should I forget about trying to implement the other part?
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index adfe9b1..f459996 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -624,6 +624,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	json_table_plan_cross
 	json_table_plan_primary
 	json_table_default_plan
+	json_path_specification
 
 %type 		json_arguments
 	json_passing_clause_opt
@@ -635,8 +636,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type 		json_returning_clause_opt
 
-%type 			json_path_specification
-	json_table_column_path_specification_clause_opt
+%type 			json_table_column_path_specification_clause_opt
 	json_table_path_name
 	json_as_path_name_clause_opt
 
@@ -845,6 +845,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
  */
 %nonassoc	UNBOUNDED		/* ideally should have same precedence as IDENT */
 %nonassoc	ERROR_P EMPTY_P DEFAULT ABSENT /* JSON error/empty behavior */
+%nonassoc	COLUMNS FALSE_P KEEP OMIT PASSING TRUE_P UNKNOWN
 %nonassoc	IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP
 %left		Op OPERATOR		/* multi-character ops and user-defined operators */
 %left		'+' '-'
@@ -14472,7 +14473,7 @@ json_context_item:
 		;
 
 json_path_specification:
-			Sconst	{ $$ = $1; }
+			a_expr	{ $$ = $1; }
 		;
 
 json_as_path_name_clause_opt:
@@ -14802,7 +14803,7 @@ json_table_formatted_column_definition:
 		;
 
 json_table_nested_columns:
-			NESTED path_opt json_path_specification
+			NESTED path_opt Sconst
 			json_as_path_name_clause_opt
 			json_table_columns_clause
 {

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

Hi

2017-11-02 3:39 GMT+01:00 Peter Eisentraut :

> Could someone clarify the status of this patch set?  It has been in
> "Waiting" mode since the previous CF and no new patch, just a few
> questions from the author.
>

There was a state "needs review". I looked to the patch, and found some
issues, so I sent mail about these issues and switched state to "waiting on
author"

Regards

Pavel

>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[Peter Eisentraut]

Could someone clarify the status of this patch set?  It has been in
"Waiting" mode since the previous CF and no new patch, just a few
questions from the author.

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Nikita Glukhov]

Hi, hackers!

I have a question about transformation of JSON constructors into executor nodes.

In first letter in this thread we wrote:
   JSON_OBJECT(), JSON_ARRAY() constructors and IS JSON predicate are
   transformed into raw function calls.

Here is an example explaining what it means:

=# CREATE VIEW json_object_view AS
SELECT JSON_OBJECT('foo': 1, 'bar': '[1,2]' FORMAT JSON RETURNING text);
CREATE VIEW
=# \sv json_object_view
CREATE OR REPLACE VIEW public.json_object_view AS
 SELECT json_build_object_ext(false, false, 'foo', 1, 'bar', 
'[1,2]'::text::json)::text

As you can see JSON_OBJECT() was transformed into a call on new function
json_build_object_ext(), which shares a code with existing json_build_object()
but differs from it only by two additional boolean parameters for
representation of  {WITH|WITHOUT} UNIQUE [KEYS] and {NULL|ABSENT} ON NULL
clauses.  Information about FORMAT, RETURNING clauses was lost, since they
were transformed into casts.

Other constructors are transformed similary:
JSON_ARRAY() => json[b]_build_array_ext(boolean, VARIADIC any)
JSON_OBJECTAGG() => json[b]_objectagg(any, any, boolean, boolean)
JSON_ARRAYAGG()  => json[b]_agg[_strict](any)

Also there is a variant of JSON_ARRAY() with subquery which transformed into a
subselect with json[b]_agg():
=# CREATE VIEW json_array_view AS SELECT JSON_ARRAY(SELECT 
generate_series(1,3));
CREATE VIEW
=# \sv json_array_view
CREATE OR REPLACE VIEW public.json_array_view AS
 SELECT ( SELECT json_agg_strict(q.a)
   FROM ( SELECT generate_series(1, 3) AS generate_series) q(a))



And here is my question: is it acceptable to do such transformations?
And if is not acceptable (it seemed unacceptable to us from the beginning,
but we did not have time for correct implementation), how should JSON
constructor nodes look like?


The simplest solution that I can propose is to save both transformed
expressions in existing JsonObjectCtor/JsonArrayCtor nodes which exist
now only in untransformed trees.  Whole untransformed JsonXxxCtor node
will be used for displaying, transformed expression -- for execution only.

But it will not work for aggregates, because they are transformed into a
Aggref/WindowFunc node.  Information needed for correct displaying should be
saved somewhere in these standard nodes.

And for subquery variant of JSON_ARRAY I can only offer to leave transformation
into a subselect with JSON_ARRAYAGG():
JSON_ARRAY(query) => (SELECT JSON_ARRAYAGG(bar) FROM (query) foo(bar))

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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

2017-09-30 1:06 GMT+02:00 Nikita Glukhov :

> On 29.09.2017 20:07, Pavel Stehule wrote:
>
> 2017-09-29 12:15 GMT+02:00 Pavel Stehule :
>
>>
>> 2017-09-29 12:09 GMT+02:00 Nikita Glukhov :
>>
>>>
>>>
>>> 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.
>>>
>>>
>>
>> ok
>>
>> Thank you
>>
>
> I have few queries and notes
>
> 1. Why first patch holds Gin related functionality? Can be it separated?
>
> Yes, it can be easily separated. Attached archive with separated GIN patch
> no.2.
>
> 2. Why Json path functions starts by "_" ? These functions are not removed
> by other patches.
>
> Originally, these functions were created only for testing purposes and
> should
> be treated as "internal". But with introduction of jsonpath operators
> jsonpath
> tests can be completely rewritten using this operators.
>

yes - it should be removed.

Probably separation to jsonpath and sqljson is not happy (or sqljson part
should not contains JSON_QUERY and related functions).

Why this code is in patch?

















*+/Example functions for
JsonPath***/++static Datum+returnDATUM(void *arg,
bool *isNull)+{+<->*isNull =
false;+<->return<>PointerGetDatum(arg);+}++static Datum+returnNULL(void
*arg, bool *isNull)+{+<->*isNull = true;+<->return Int32GetDatum(0);+}+*
Regards

Pavel


> 3. What is base for jsonpath-extensions? ANSI/SQL?
>
> Our jsonpath extensions are not based on any standards, so they are quite
> dangerous because they can conflict with the standard in the future.
>
> This patch is pretty big - so I propose to push JSONPath and SQL/JSON
> related patches first, and then in next iteration to push JSON_TABLE patch.
> Is it acceptable strategy?
>
> I think it's acceptable. And this was the main reason for the separation
> of patches.
>
> I am sure so JSON_TABLE is pretty important function, but it is pretty
> complex too (significantly more complex than XMLTABLE), so it can be
> practiacal to move this function to separate project. I hope so all patches
> will be merged in release 11 time.
>
>
> --
> Nikita Glukhov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

2017-09-30 1:06 GMT+02:00 Nikita Glukhov :

> On 29.09.2017 20:07, Pavel Stehule wrote:
>
> 2017-09-29 12:15 GMT+02:00 Pavel Stehule :
>
>>
>> 2017-09-29 12:09 GMT+02:00 Nikita Glukhov :
>>
>>>
>>>
>>> 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.
>>>
>>>
>>
>> ok
>>
>> Thank you
>>
>
> I have few queries and notes
>
> 1. Why first patch holds Gin related functionality? Can be it separated?
>
> Yes, it can be easily separated. Attached archive with separated GIN patch
> no.2.
>
> 2. Why Json path functions starts by "_" ? These functions are not removed
> by other patches.
>
> Originally, these functions were created only for testing purposes and
> should
> be treated as "internal". But with introduction of jsonpath operators
> jsonpath
> tests can be completely rewritten using this operators.
>
> 3. What is base for jsonpath-extensions? ANSI/SQL?
>
> Our jsonpath extensions are not based on any standards, so they are quite
> dangerous because they can conflict with the standard in the future.
>
> This patch is pretty big - so I propose to push JSONPath and SQL/JSON
> related patches first, and then in next iteration to push JSON_TABLE patch.
> Is it acceptable strategy?
>
> I think it's acceptable. And this was the main reason for the separation
> of patches.
>

I prefer to move it to another commit fest item. It will simplify a
communication between us and possible committers - and we can better
concentrate to smaller set of code.



> I am sure so JSON_TABLE is pretty important function, but it is pretty
> complex too (significantly more complex than XMLTABLE), so it can be
> practiacal to move this function to separate project. I hope so all patches
> will be merged in release 11 time.
>
>
> --
> Nikita Glukhov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

2017-09-29 12:15 GMT+02:00 Pavel Stehule :

>
>
> 2017-09-29 12:09 GMT+02:00 Nikita Glukhov :
>
>>
>>
>> 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.
>>
>>
>
> ok
>
> Thank you
>

I have few queries and notes

1. Why first patch holds Gin related functionality? Can be it separated?

2. Why Json path functions starts by "_" ? These functions are not removed
by other patches.

3. What is base for jsonpath-extensions? ANSI/SQL?

This patch is pretty big - so I propose to push JSONPath and SQL/JSON
related patches first, and then in next iteration to push JSON_TABLE patch.
Is it acceptable strategy? I am sure so JSON_TABLE is pretty important
function, but it is pretty complex too (significantly more complex than
XMLTABLE), so it can be practiacal to move this function to separate
project. I hope so all patches will be merged in release 11 time.

Regards

Pavel



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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

2017-09-29 12:09 GMT+02:00 Nikita Glukhov :

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

ok

Thank you

Pavel

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Nikita Glukhov]

On 29.09.2017 12:59, Pavel Stehule wrote:


Hi

2017-09-16 1:31 GMT+02:00 Nikita Glukhov >:


On 15.09.2017 22:36, Oleg Bartunov wrote:

On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas
> wrote:

On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson
> 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.


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.


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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

Hi

2017-09-16 1:31 GMT+02:00 Nikita Glukhov :

> On 15.09.2017 22:36, Oleg Bartunov wrote:
>
> On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas 
>> wrote:
>>
>>> On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson 
>>> 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/c0
>> 67367_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.
>
>
I have some free time now. Is it last version?

Regards

Pavel

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Nikita Glukhov]

On 18.09.2017 00:38, Alvaro Herrera wrote:


Nikita Glukhov wrote:


0007-json_table-v02.patch:
  - JSON_TABLE using XMLTABLE infrastructure

0008-json_table-json-v02.patch:
  - JSON_TABLE support for json type

I'm confused ... why are these two patches and not a single one?


As I sad before, json support in jsonpath looks a bit dubious to me.  So if
patch no. 2 will not be accepted, then patches no. 4, 6, 8 should also be
simply skipped.  But, of course, patches 1 and 2, 3 and 4, 5 and 6, 7 and 8
can be combined.

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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL/JSON in PostgreSQL

[Alvaro Herrera]

Nikita Glukhov wrote:

> 0007-json_table-v02.patch:
>  - JSON_TABLE using XMLTABLE infrastructure
> 
> 0008-json_table-json-v02.patch:
>  - JSON_TABLE support for json type

I'm confused ... why are these two patches and not a single one?

-- 
Álvaro Herrerahttps://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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Alexander Korotkov]

On Sun, Sep 17, 2017 at 11:08 AM, Oleg Bartunov  wrote:

> On 16 Sep 2017 02:32, "Nikita Glukhov"  wrote:
>
> On 15.09.2017 22:36, Oleg Bartunov wrote:
>
> On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas 
>> wrote:
>>
>>> On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson 
>>> 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/c0
>> 67367_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
>
>
> We provide web interface to our build
> http://sqlfiddle.postgrespro.ru/#!21/
>

+1,
For experimenting with SQL/JSON select "PostgreSQL 10dev+SQL/JSON" in the
version select field on top toolbar.

--
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On 16 Sep 2017 02:32, "Nikita Glukhov"  wrote:

On 15.09.2017 22:36, Oleg Bartunov wrote:

On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas  wrote:
>
>> On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson 
>> 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/c0
> 67367_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


We provide web interface to our build
http://sqlfiddle.postgrespro.ru/#!21/



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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas  wrote:
> On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson  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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Robert Haas]

On Fri, Sep 15, 2017 at 10:10 AM, Daniel Gustafsson  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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Daniel Gustafsson]

> On 15 Aug 2017, at 04:30, Peter Eisentraut  
> 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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Peter Eisentraut]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[David Steele]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On Mon, Mar 13, 2017 at 9:24 AM, 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...
>

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. HH24:MI TZH") ? (@ < "10.03.2017 12:35
+1".datetime("dd.mm. HH24:MI TZH"))'
);
 _jsonpath_object
--
 "2017-03-10 14:34:00+03"
 "2017-03-10 13:35:00+03"
(2 rows)

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

2017-03-13 7:24 GMT+01:00 Nico Williams :

> 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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Nico Williams]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Nico Williams]

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

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Nico Williams]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On Fri, Mar 10, 2017 at 7:07 AM, Petr Jelinek 
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.



>
> --
>   Petr Jelinek  http://www.2ndQuadrant.com/
>   PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[Josh Berkus]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Magnus Hagander]

On Thu, Mar 9, 2017 at 1:12 PM, 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  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/

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Petr Jelinek]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Peter van Hardenberg]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

On 08.03.2017 20:52, Magnus Hagander wrote:
On Wed, Mar 8, 2017 at 11:48 AM, Peter van Hardenberg > 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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Robert Haas]

On Thu, Mar 9, 2017 at 12:48 PM, Sven R. Kunze  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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Robert Haas]

On Tue, Mar 7, 2017 at 2:38 PM, Andres Freund  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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Magnus Hagander]

On Wed, Mar 8, 2017 at 11:48 AM, Peter van Hardenberg  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/

Re: [HACKERS] SQL/JSON in PostgreSQL

[Peter van Hardenberg]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On Wed, Mar 8, 2017 at 7:05 AM, 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.
>

Yes, after committing XMLTABLE, we anyway need to extend its infrastructure
to support JSON_TABLE.


>
> --
> -David
> da...@pgmasters.net
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On Wed, Mar 8, 2017 at 12:43 AM, Sven R. Kunze  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 "-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  [  ] 
| keyvalue  

 ::=


datetime template is also specified in the standard (very rich)

 ::=
{  }...
 ::=

| 
 ::=

| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
 ::=

| 
| 
| 
| 
| 
| 
| 
 ::=
 | YYY | YY | Y
 ::=
 | RR
 ::=
MM
 ::=
DD
 ::=
DDD
 ::=
HH | HH12
 ::=
HH24
 ::=
MI
 ::=
SS
 ::=
S
 ::=
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
 ::=
A.M. | P.M.
 ::=
TZH
 ::=
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.


>
> Regards,
> Sven
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[David Steele]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Sven R. Kunze]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Andres Freund]

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On Fri, Mar 3, 2017 at 11:49 PM, David Steele  wrote:

> Hi Oleg,
>
> On 2/28/17 2:55 PM, Pavel Stehule wrote:
> > 2017-02-28 20:08 GMT+01:00 Oleg Bartunov  >
> > 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
> >  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.


>
> Regards,
> --
> -David
> da...@pgmasters.net
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

2017-03-03 21:49 GMT+01:00 David Steele :

> Hi Oleg,
>
> On 2/28/17 2:55 PM, Pavel Stehule wrote:
> > 2017-02-28 20:08 GMT+01:00 Oleg Bartunov  >
> > 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
> >  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


> Regards,
> --
> -David
> da...@pgmasters.net
>

Re: [HACKERS] SQL/JSON in PostgreSQL

[David Steele]

Hi Oleg,

On 2/28/17 2:55 PM, Pavel Stehule wrote:
> 2017-02-28 20:08 GMT+01:00 Oleg Bartunov  
> 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.

<...>

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

>
>
>
>1.
>
>Added explicit casts bytea=>jsonb and jsonb=>bytea (for jsonb=>bytea
>output using RETURNING bytea FORMAT JSONB and corresponding bytea=>jsonb
>input using  FORMAT JSONB).
>
>
This point has sense in Oracle, where JSON is blob. But it is little bit
obscure in PostgreSQL context.

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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

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



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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Oleg Bartunov]

On Tue, Feb 28, 2017 at 10:55 PM, Pavel Stehule 
wrote:

> Hi
>
>
> 2017-02-28 20:08 GMT+01:00 Oleg Bartunov :
>
>> 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:
>>
>> — : extracts an SQL value of a predefined type from
>> a JSON text.
>>
>> — : extracts a JSON text from a JSON text.
>>
>> — : converts a JSON text to an SQL table.
>>
>> — : tests whether a string value is or is not properly
>> formed JSON text.
>>
>> — : tests whether an SQL/JSON path expression
>> returns any SQL/JSON items.
>>
>> The SQL/JSON construction functions are:
>>
>> — : generates a string that is a serialization
>> of an SQL/JSON object.
>>
>> — : generates a string that is a serialization of
>> an SQL/JSON array.
>>
>> — : generates, from an aggregation of
>> SQL data, a string that is a serialization
>>
>> of an SQL/JSON object.
>>
>> — : 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

Re: [HACKERS] SQL/JSON in PostgreSQL

[Pavel Stehule]

Hi


2017-02-28 20:08 GMT+01:00 Oleg Bartunov :

> 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:
>
> — : extracts an SQL value of a predefined type from a
> JSON text.
>
> — : extracts a JSON text from a JSON text.
>
> — : converts a JSON text to an SQL table.
>
> — : tests whether a string value is or is not properly
> formed JSON text.
>
> — : tests whether an SQL/JSON path expression
> returns any SQL/JSON items.
>
> The SQL/JSON construction functions are:
>
> — : generates a string that is a serialization of
> an SQL/JSON object.
>
> — : generates a string that is a serialization of
> an SQL/JSON array.
>
> — : generates, from an aggregation of
> SQL data, a string that is a serialization
>
> of an SQL/JSON object.
>
> — : 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  VALUE , ...) doesn’t
>