Re: SQL/JSON: documentation

Attached patch with draft of SQL/JSON documentation written by
Liudmila Mantrova, Oleg Bartunov and me.

Also it can be found in our sqljson repository on sqljson_doc branch:
https://github.com/postgrespro/sqljson/tree/sqljson_doc

We continue to work on it.

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

On 06/27/2018 07:36 PM, Nikita Glukhov wrote:

> Also it can be found in our sqljson repository on sqljson_doc branch:
> https://github.com/postgrespro/sqljson/tree/sqljson_doc

Perhaps it is my unfamiliarity, but it seems that on lines 1067–1071,
the output clause for JSON_VALUE is given support for return types
json, jsonb, bytea, text, char, varchar, nchar "out of the box".

There are then examples on lines 1123–1135 of returning float, int,
and date.

Does that mean that the list in 1067–1071 is incomplete, and should
include additional data types?

Or does it mean that there is more cleverness buried in the
"must ... have a cast to the specified type" language than I
first understood?

Does the function support returning some wanted type w, not in the
out-of-the-box list, such as float, by searching for an intermediate
type t ∈ {json, jsonb, bytea, text, char, varchar, nchar} such that
∃ cast(t as w), then representing the JSON value as t, then casting
that to w ?

If so, what does it do if more than one t is a candidate?

Line 2081: "A typical path expression has the following structure"

It seems like a "weasel word" to have "typical" in the statement
of an expression grammar. Is there more to the grammar than is
given here?

Lines 2323 and 2330 ( / and % operators ). Do these behave differently
for integer than for float operands? If they provide integer operations,
which results do they produce for negative operands? (A recent minor
trauma reminded me that C before C99 left that unspecified, but as this
is a special-purpose language, perhaps there is a chance to avoid
leaving such details vague. :) For a similar-language example,
XPath/XQuery specifies that its idiv and mod operators have the
truncate-quotient-toward-zero semantics, regardless of the signs of
the operands.

Line 2519, like_regex: What regex dialect is accepted here? The same
as the PostgreSQL "POSIX regex"? Or some other?

This looks like very interesting functionality!

-Chap

On 28.06.2018 05:23, Chapman Flack wrote:

> On 06/27/2018 07:36 PM, Nikita Glukhov wrote:
>
>> Also it can be found in our sqljson repository on sqljson_doc branch:
>> https://github.com/postgrespro/sqljson/tree/sqljson_doc
> Perhaps it is my unfamiliarity, but it seems that on lines 1067–1071,
> the output clause for JSON_VALUE is given support for return types
> json, jsonb, bytea, text, char, varchar, nchar "out of the box".
>
> There are then examples on lines 1123–1135 of returning float, int,
> and date.
>
> Does that mean that the list in 1067–1071 is incomplete, and should
> include additional data types?
>
> Or does it mean that there is more cleverness buried in the
> "must ... have a cast to the specified type" language than I
> first understood?
>
> Does the function support returning some wanted type w, not in the
> out-of-the-box list, such as float, by searching for an intermediate
> type t ∈ {json, jsonb, bytea, text, char, varchar, nchar} such that
> ∃ cast(t as w), then representing the JSON value as t, then casting
> that to w ?
>
> If so, what does it do if more than one t is a candidate?

First, thank you for your interest in SQL/JSON docs.

Standard says only about returning of string (both binary and character),
numeric, boolean and datetime types in JSON_VALUE and only about string
types in JSON_QUERY.

In JSON_VALUE first searched cast from the SQL type corresponding to the
SQL/JSON type of a resulting scalar item to the target RETURNING type.

SQL/JSON type PG SQL type
string => text
number => numeric
boolean => boolean
date => date
time => time
time with tz => timetz
timestamp => timestamp
timestamp with tz => timestamptz

If this cast does not exist then conversion via input/output is tried (this
is our extension). But json and jsonb RETURNING types are exceptional here,
because SQL/JSON items can be converted directly to json[b] without casting.

But we also support returning of arbitrary PG types including arrays, domains
and records in both JSON_VALUE and JSON_QUERY. In JSON_VALUE values of this
types should be represented as serialized JSON strings, because JSON_VALUE
supports only returning of scalar items. The behavior of JSON_QUERY is similar
to the behavior json[b]_populate_record().

Examples:

-- CAST(numeric AS int) is used here
=# SELECT JSON_VALUE('1.8', '$' RETURNING int);
json_value
------------
2
(1 row)

-- CAST(text AS int) is used here
=# SELECT JSON_VALUE('"1"', '$' RETURNING int);
json_value
------------
1
(1 row)

-- CAST(text AS int) is used here
=# SELECT JSON_VALUE('"1.8"', '$' RETURNING int ERROR ON ERROR);
ERROR: invalid input syntax for integer: "1.8"

-- CAST(numeric AS int) is used here
# SELECT JSON_VALUE('"1.8"', '$.double().floor()' RETURNING int);
json_value
------------
1
(1 row)

-- array of points serialized into single JSON string
-- CAST(text AS point[]) is used
=# SELECT JSON_VALUE('"{\"(1,2)\",\"3,4\",NULL}"', '$' RETURNING point[]);
json_value
------------------------
{"(1,2)","(3,4)",NULL}
(1 row)

-- point[] is represented by JSON array of point strings
-- ARRAY[CAST(text AS point)] is used
=# SELECT JSON_QUERY('["(1, 2)", " 3 , 4 ", null]', '$' RETURNING point[]);

json_query
------------------------
{"(1,2)","(3,4)",NULL}
(1 row)

-- JSON object converted into SQL record type
=# SELECT JSON_QUERY('{"relname": "foo", "relnatts" : 5}', '$' RETURNING pg_class);
json_query
----------------------------------------
(foo,,,,,,,,,,,,,,,,5,,,,,,,,,,,,,,,,)
(1 row)

> Line 2081: "A typical path expression has the following structure"
>
> It seems like a "weasel word" to have "typical" in the statement
> of an expression grammar. Is there more to the grammar than is
> given here?

Yes, that expression grammar is incomplete because arithmetic operations
are supported on the top of jsonpath accessor expressions.

Here is nearly complete expression grammar (predicates are not included):

jsonpath ::=
[STRICT | LAX] jsonpath_expression

jsonpath_expression ::=
jsonpath_additive_expression

jsonpath_additive_expression ::=
[ jsonpath_additive_expression { + | - } ]
jsonpath_multiplicative_expression

jsonpath_multiplicative_expression ::=
[ jsonpath_multiplicative_expression { * | / | % } ]
jsonpath_unary_expression

jsonpath_unary_expression ::=
jsonpath_accessor_expression
| { + | - } jsonpath_unary_expression

jsonpath_accessor_expression ::=
jsonpath_primary { jsonpath_accessor }[...]

jsonpath_accessor ::=
  . *
  | . key_name
| . method_name ( jsonpath_expression [, ...] )
| '[' * ']'
| '[' jsonpath_expression [, ...] ']'
| ? ( predicate )

jsonpath_primary ::=
$
 | @
| variable
 | literal
| ( jsonpath_expression )

> Lines 2323 and 2330 ( / and % operators ). Do these behave differently
> for integer than for float operands? If they provide integer operations,
> which results do they produce for negative operands? (A recent minor
> trauma reminded me that C before C99 left that unspecified, but as this
> is a special-purpose language, perhaps there is a chance to avoid
> leaving such details vague. :) For a similar-language example,
> XPath/XQuery specifies that its idiv and mod operators have the
> truncate-quotient-toward-zero semantics, regardless of the signs of
> the operands.

Arithmetic operations in jsonpath are implemented using PG numeric datatype,
which also is used in jsonb for representation of JSON numbers:

=# SELECT jsonb '3' @* '$ / 2';
?column?
--------------------
1.5000000000000000
(1 row)

=# SELECT jsonb '3.4' @* '$ % 2.3';
?column?
----------
1.1
(1 row)

=# SELECT jsonb '-3.4' @* '$ % 2.3';
?column?
----------
-1.1
(1 row)

The same behavior exists in JavaScript, but it seems that ordinary double
type is used there.

> Line 2519, like_regex: What regex dialect is accepted here? The same
> as the PostgreSQL "POSIX regex"? Or some other?

Standard requires XQuery regexes, but we have only POSIX regexes in PostgreSQL
now, so we decided to use the latter.

We will fix all these issues soon.

> This looks like very interesting functionality!
>
> -Chap

You can try this SQL/JSON examples in our web interface:
http://sqlfiddle.postgrespro.ru/#!21/
(please first select "PostgreSQL 11dev+SQL/JSON" in the version
selection field on the top toolbar).

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

On 06/28/2018 06:45 PM, Nikita Glukhov wrote:

> Standard says only about returning of string (both binary and character),
> numeric, boolean and datetime types in JSON_VALUE and only about string
> types in JSON_QUERY.

What I think I noticed was that right now, in func-sqljson.sgml,
the same list of seven types (not including numeric, boolean, or
datetime) is repeated for both JSON_QUERY and JSON_VALUE. Should
the list for JSON_VALUE also mention that numeric, boolean, and
datetime are supported there? That's the description that is near
line 1067.

> Arithmetic operations in jsonpath are implemented using PG numeric
> datatype,
> which also is used in jsonb for representation of JSON numbers:
> ...
> =# SELECT jsonb '-3.4' @* '$ % 2.3';
>  ?column?
> ----------
>  -1.1

In a recent message[1] it seemed that PG itself relies on the
underlying C compiler behavior, at least for int and float, which
could mean that on some platforms the answer is -1.1 and on others
+1.2. But I don't know whether that is true for PG numeric, since
that is implemented much more within PG itself, so perhaps it has
a platform-independent behavior. The XQuery result would be -1.1 on
all platforms, because the standard is explicit there.

-Chap

[1]: https://www.postgresql.org/message-id/23660.1530070402%40sss.pgh.pa.us

On 28/06/2018 01:36, Nikita Glukhov wrote:
> Attached patch with draft of SQL/JSON documentation written by
> Liudmila Mantrova, Oleg Bartunov and me.
>
> Also it can be found in our sqljson repository on sqljson_doc branch:
> https://github.com/postgrespro/sqljson/tree/sqljson_doc
>
> We continue to work on it.

Some structural comments:

- I don't think this should be moved to a separate file. Yes, func.sgml
is pretty big, but if we're going to split it up, we should do it in a
systematic way, not just one section.

- The refentries are not a bad idea, but again, if we just used them for
this one section, the navigation will behave weirdly. So I'd do it
without them, just using normal subsections.

- Stick to one-space indentation in XML.

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

On 09/28/2018 01:29 PM, Peter Eisentraut wrote:
> On 28/06/2018 01:36, Nikita Glukhov wrote:
>> Attached patch with draft of SQL/JSON documentation written by
>> Liudmila Mantrova, Oleg Bartunov and me.
>>
>> Also it can be found in our sqljson repository on sqljson_doc branch:
>> https://github.com/postgrespro/sqljson/tree/sqljson_doc
>>
>> We continue to work on it.
> Some structural comments:
>
> - I don't think this should be moved to a separate file. Yes, func.sgml
> is pretty big, but if we're going to split it up, we should do it in a
> systematic way, not just one section.

I'm in favor of doing that. It's rather a monster.

I agree it should not be done piecemeal.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com> writes:
> On 09/28/2018 01:29 PM, Peter Eisentraut wrote:
>> - I don't think this should be moved to a separate file. Yes, func.sgml
>> is pretty big, but if we're going to split it up, we should do it in a
>> systematic way, not just one section.

> I'm in favor of doing that. It's rather a monster.
> I agree it should not be done piecemeal.

Maybe split it into one file per existing section?

Although TBH, I am not convinced that the benefits of doing that
will exceed the back-patching pain we'll incur.

regards, tom lane

On 09/28/2018 08:29 PM, Peter Eisentraut wrote:
> On 28/06/2018 01:36, Nikita Glukhov wrote:
>> Attached patch with draft of SQL/JSON documentation written by
>> Liudmila Mantrova, Oleg Bartunov and me.
>>
>> Also it can be found in our sqljson repository on sqljson_doc branch:
>> https://github.com/postgrespro/sqljson/tree/sqljson_doc
>>
>> We continue to work on it.
> Some structural comments:
>
> - I don't think this should be moved to a separate file. Yes, func.sgml
> is pretty big, but if we're going to split it up, we should do it in a
> systematic way, not just one section.
>
> - The refentries are not a bad idea, but again, if we just used them for
> this one section, the navigation will behave weirdly. So I'd do it
> without them, just using normal subsections.
>
> - Stick to one-space indentation in XML.
>
Hi Peter,

Thanks for your comments! I'm OK with keeping all reference information
in func.sgml and will rework it as you suggest. While refentries are
dear to my heart, let's use subsections for now for the sake of
consistency. We'll continue working with Nikita and Oleg to improve the
content before we resend an updated patch; I believe we might still need
a separate source file if we end up having a separate chapter with usage
examples and implementation details.

--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

> On Mon, Oct 1, 2018 at 2:24 PM Liudmila Mantrova <l(dot)mantrova(at)postgrespro(dot)ru> wrote:
>
> We'll continue working with Nikita and Oleg to improve the
> content before we resend an updated patch; I believe we might still need
> a separate source file if we end up having a separate chapter with usage
> examples and implementation details.

Hi,

Any progress on that? It would be nice to have a new version of the
documentation, and I would even advocate to put it into the json path patch [1]
(especially, since there were already requests for that, and I personally don't
see any reason to keep them separately). For now I'll move the item to
the next CF.

[1]: https://www.postgresql.org/message-id/flat/fcc6fc6a-b497-f39a-923d-aa34d0c588e8%402ndQuadrant.com

On 11/29/18 7:34 PM, Dmitry Dolgov wrote:
> Hi,
>
> Any progress on that? It would be nice to have a new version of the
> documentation, and I would even advocate to put it into the json path patch [1]
> (especially, since there were already requests for that, and I personally don't
> see any reason to keep them separately). For now I'll move the item to
> the next CF.
>
> [1]:https://www.postgresql.org/message-id/flat/fcc6fc6a-b497-f39a-923d-aa34d0c588e8%402ndQuadrant.com

Hi Dmitry,

Unfortunately, I couldn't find much time for this activity, but as far
as I understand, thread [1] only requires jsonpath documentation right
now. So I extracted the relevant parts from this patch, reworked path
expression description, and moved it to func.sgml as Peter suggested
(attached). Nikita is going to add this patch to the jsonpath thread
together with the updated code once it's ready.

Next, I'm going to address Peter's feedback on the rest of this
documentation patch (which probably also needs to be split for threads
[2] and [3]).

[2]
https://www.postgresql.org/message-id/flat/cd0bb935-0158-78a7-08b5-904886deac4b(at)postgrespro(dot)ru
[3]
https://www.postgresql.org/message-id/flat/132f26c4-dfc6-f8fd-4764-2cbf455a3aec(at)postgrespro(dot)ru

--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Mon, Dec 03, 2018 at 07:23:09PM +0300, Liudmila Mantrova wrote:
> Unfortunately, I couldn't find much time for this activity, but as far as I
> understand, thread [1] only requires jsonpath documentation right now. So I
> extracted the relevant parts from this patch, reworked path expression
> description, and moved it to func.sgml as Peter suggested (attached). Nikita
> is going to add this patch to the jsonpath thread together with the updated
> code once it's ready.

For now the entry is marked as returned with feedback.
--
Michael