Re: SQL/JSON documentation JSON_TABLE
On 2022-07-15 Fr 02:20, Erik Rijkers wrote: > On 7/14/22 17:45, Andrew Dunstan wrote: >> >> >> Here's a patch that deals with most of this. There's one change you >> wanted that I don't think is correct, which I omitted. >> >> [json-docs-fix.patch] > > Thanks, much better. I also agree that the change I proposed (and you > omitted) wasn't great (although it leaves the paragraph somewhat > orphaned - but maybe it isn't too bad.). > > I've now compared our present document not only with the original doc > as produced by Nikita Glukhov et al in 2018, but also with the ISO > draft from 2017 (ISO/IEC TR 19075-6 (JSON) for JavaScript Object). > > I think we can learn a few things from that ISO draft's JSON_TABLE > text. Let me copy-paste its first explicatory paragraph on JSON_TABLE: > > -- [ ISO SQL/JSON draft 2017 ] - > Like the other JSON querying operators, JSON_TABLE begins with API common syntax> to specify the context item, path expression and > PASSING clause. The path expression in this case is more accurately > called the row pattern path expression. This path expression is > intended to produce an SQL/JSON sequence, with one SQL/JSON item for > each row of the output table. > > The COLUMNS clause can define two kinds of columns: ordinality columns > and regular columns. > > An ordinality column provides a sequential numbering of rows. Row > numbering is 1-based. > > A regular column supports columns of scalar type. The column is > produced using the semantics of JSON_VALUE. The column has an optional > path expression, called the column pattern, which can be defaulted > from the column name. The column pattern is used to search for the > column within the current SQL/JSON item produced by the row pattern. > The column also has optional ON EMPTY and ON ERROR clauses, with the > same choices and semantics as JSON_VALUE. > -- > > > So, where the ISO draft introduces the term 'row pattern' it /also/ > introduces the term 'column pattern' close by, in the next paragraph. > > I think our docs too should have both terms. The presence of both > 'row pattern' and 'column pattern' immediately makes their meanings > obvious. At the moment our docs only use the term 'row pattern', for > all the JSON_TABLE json path expressions (also those in the COLUMN > clause, it seems). > > > At the moment, we say, in the JSON_TABLE doc: > > To split the row pattern into columns, json_table provides the COLUMNS > clause that defines the schema of the created view. > > > I think that to use 'row pattern' here is just wrong, or at least > confusing. The 'row pattern' is /not/ the data as produced from the > json expression; the 'row pattern' /is/ the json path expression. > (ISO draft: 'The path expression in this case is more accurately > called the row pattern path expression.' ) > > If you agree with my reasoning I can try to rewrite these bits in our > docs accordingly. > > > Yes, please do. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: SQL/JSON documentation JSON_TABLE
On 7/14/22 17:45, Andrew Dunstan wrote:
On 2022-07-08 Fr 16:20, Andrew Dunstan wrote:
On 2022-07-08 Fr 16:03, Erik Rijkers wrote:
Hi,
Attached are a few small changes to the JSON_TABLE section in func.sgml.
The first two changes are simple typos.
Then there was this line:
context_item, path_expression [ AS json_path_name ] [ PASSING { value
AS varname } [, ...]]
those are the parameters to JSON_TABLE() so I changed that line to:
JSON_TABLE(context_item, path_expression [ AS json_path_name ] [
PASSING { value AS varname } [, ...]])
Some parts of the JSON_TABLE text strike me as opaque. For instance,
there are paragraphs that more than once use the term:
json_api_common_syntax
'json_api_common_syntax' is not explained. It turns out it's a relic
from Nikita's original docs. I dug up a 2018 patch where the term is
used as:
2018:
JSON_TABLE (
json_api_common_syntax [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
(etc...)
with explanation:
2018:
json_api_common_syntax:
The input data to query, the JSON path expression defining the
query, and an optional PASSING clause.
So that made sense then (input+jsonpath+params=api), but it doesn't
now fit as such in the current docs.
I think it would be best to remove all uses of that compound term, and
rewrite the explanations using only the current parameter names
(context_item, path_expression, etc).
Thanks for this. If you want to follow up that last sentence I will try
to commit a single fix early next week.
Here's a patch that deals with most of this. There's one change you
wanted that I don't think is correct, which I omitted.
[json-docs-fix.patch]
Thanks, much better. I also agree that the change I proposed (and you
omitted) wasn't great (although it leaves the paragraph somewhat
orphaned - but maybe it isn't too bad.).
I've now compared our present document not only with the original doc as
produced by Nikita Glukhov et al in 2018, but also with the ISO draft
from 2017 (ISO/IEC TR 19075-6 (JSON) for JavaScript Object).
I think we can learn a few things from that ISO draft's JSON_TABLE text.
Let me copy-paste its first explicatory paragraph on JSON_TABLE:
-- [ ISO SQL/JSON draft 2017 ] -
Like the other JSON querying operators, JSON_TABLE begins with common syntax> to specify the context item, path expression and PASSING
clause. The path expression in this case is more accurately called the
row pattern path expression. This path expression is intended to produce
an SQL/JSON sequence, with one SQL/JSON item for each row of the output
table.
The COLUMNS clause can define two kinds of columns: ordinality columns
and regular columns.
An ordinality column provides a sequential numbering of rows. Row
numbering is 1-based.
A regular column supports columns of scalar type. The column is produced
using the semantics of JSON_VALUE. The column has an optional path
expression, called the column pattern, which can be defaulted from the
column name. The column pattern is used to search for the column within
the current SQL/JSON item produced by the row pattern. The column also
has optional ON EMPTY and ON ERROR clauses, with the same choices and
semantics as JSON_VALUE.
--
So, where the ISO draft introduces the term 'row pattern' it /also/
introduces the term 'column pattern' close by, in the next paragraph.
I think our docs too should have both terms. The presence of both 'row
pattern' and 'column pattern' immediately makes their meanings obvious.
At the moment our docs only use the term 'row pattern', for all the
JSON_TABLE json path expressions (also those in the COLUMN clause, it
seems).
At the moment, we say, in the JSON_TABLE doc:
To split the row pattern into columns, json_table provides the COLUMNS
clause that defines the schema of the created view.
I think that to use 'row pattern' here is just wrong, or at least
confusing. The 'row pattern' is /not/ the data as produced from the
json expression; the 'row pattern' /is/ the json path expression. (ISO
draft: 'The path expression in this case is more accurately called the
row pattern path expression.' )
If you agree with my reasoning I can try to rewrite these bits in our
docs accordingly.
Erik Rijkers
Re: SQL/JSON documentation JSON_TABLE
On 2022-07-08 Fr 16:20, Andrew Dunstan wrote:
> On 2022-07-08 Fr 16:03, Erik Rijkers wrote:
>> Hi,
>>
>> Attached are a few small changes to the JSON_TABLE section in func.sgml.
>>
>> The first two changes are simple typos.
>>
>> Then there was this line:
>>
>>
>> context_item, path_expression [ AS json_path_name ] [ PASSING { value
>> AS varname } [, ...]]
>>
>>
>> those are the parameters to JSON_TABLE() so I changed that line to:
>>
>>
>> JSON_TABLE(context_item, path_expression [ AS json_path_name ] [
>> PASSING { value AS varname } [, ...]])
>>
>>
>> Some parts of the JSON_TABLE text strike me as opaque. For instance,
>> there are paragraphs that more than once use the term:
>> json_api_common_syntax
>>
>> 'json_api_common_syntax' is not explained. It turns out it's a relic
>> from Nikita's original docs. I dug up a 2018 patch where the term is
>> used as:
>>
>> 2018:
>> JSON_TABLE (
>> json_api_common_syntax [ AS path_name ]
>> COLUMNS ( json_table_column [, ...] )
>> (etc...)
>>
>>
>> with explanation:
>>
>> 2018:
>> json_api_common_syntax:
>> The input data to query, the JSON path expression defining the
>> query, and an optional PASSING clause.
>>
>>
>> So that made sense then (input+jsonpath+params=api), but it doesn't
>> now fit as such in the current docs.
>>
>> I think it would be best to remove all uses of that compound term, and
>> rewrite the explanations using only the current parameter names
>> (context_item, path_expression, etc).
>>
>> But I wasn't sure and I haven't done any such changes in the attached.
>>
>> Perhaps I'll give it a try during the weekend.
>>
>>
>>
>
> Thanks for this. If you want to follow up that last sentence I will try
> to commit a single fix early next week.
>
>
Here's a patch that deals with most of this. There's one change you
wanted that I don't think is correct, which I omitted.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b6783b7ad0..478d6eccd8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18030,9 +18030,9 @@ FROM
or array, but if it is CONDITIONAL it will not be
applied to a single array or object. UNCONDITIONAL
is the default.
-If the result is a a scalar string, by default the value returned will have
-surrounding quotes making it a valid JSON value. However, this behavior
-is reversed if OMIT QUOTES is specified.
+If the result is a scalar string, by default the value returned will
+have surrounding quotes making it a valid JSON value. However, this
+behavior is reversed if OMIT QUOTES is specified.
The ON ERROR and ON EMPTY
clauses have similar semantics to those clauses for
json_value.
@@ -18101,7 +18101,7 @@ FROM
columns. Columns produced by NESTED PATHs at the
same level are considered to be siblings,
while a column produced by a NESTED PATH is
- considered to be a child of the column produced by and
+ considered to be a child of the column produced by a
NESTED PATH or row expression at a higher level.
Sibling columns are always joined first. Once they are processed,
the resulting rows are joined to the parent row.
@@ -18151,9 +18151,9 @@ FROM
the specified column.
- The provided PATH expression parses the
- row pattern defined by json_api_common_syntax
- and fills the column with produced SQL/JSON items, one for each row.
+ The provided PATH expression is evaluated and
+ and the column is filled with the produced SQL/JSON items, one for each
+ row.
If the PATH expression is omitted,
JSON_TABLE uses the
$.name path expression,
@@ -18185,9 +18185,8 @@ FROM
item into each row of this column.
- The provided PATH expression parses the
- row pattern defined by json_api_common_syntax
- and fills the column with produced SQL/JSON items, one for each row.
+ The provided PATH expression is evaluated and
+ the column is filled with the produced SQL/JSON items, one for each row.
If the PATH expression is omitted,
JSON_TABLE uses the
$.name path expression,
@@ -18216,11 +18215,10 @@ FROM
Generates a column and inserts a boolean item into each row of this column.
- The provided PATH expression parses the
- row pattern defined by json_api_common_syntax,
- checks whether any SQL/JSON items were returned, and fills the column with
- resulting boolean value, one for each row.
- The specified type should have cast from
+ The provided PATH expression is evaluated,
+ a check whether any SQL/JSON items were returned is done, and
+ the column is filled with the resulting boolean value, one for each row.
+ The specified type should have a cast from the
boolean.
If the PATH expression is omitted,
Re: SQL/JSON documentation JSON_TABLE
On 2022-07-08 Fr 16:03, Erik Rijkers wrote:
> Hi,
>
> Attached are a few small changes to the JSON_TABLE section in func.sgml.
>
> The first two changes are simple typos.
>
> Then there was this line:
>
>
> context_item, path_expression [ AS json_path_name ] [ PASSING { value
> AS varname } [, ...]]
>
>
> those are the parameters to JSON_TABLE() so I changed that line to:
>
>
> JSON_TABLE(context_item, path_expression [ AS json_path_name ] [
> PASSING { value AS varname } [, ...]])
>
>
> Some parts of the JSON_TABLE text strike me as opaque. For instance,
> there are paragraphs that more than once use the term:
> json_api_common_syntax
>
> 'json_api_common_syntax' is not explained. It turns out it's a relic
> from Nikita's original docs. I dug up a 2018 patch where the term is
> used as:
>
> 2018:
> JSON_TABLE (
> json_api_common_syntax [ AS path_name ]
> COLUMNS ( json_table_column [, ...] )
> (etc...)
>
>
> with explanation:
>
> 2018:
> json_api_common_syntax:
> The input data to query, the JSON path expression defining the
> query, and an optional PASSING clause.
>
>
> So that made sense then (input+jsonpath+params=api), but it doesn't
> now fit as such in the current docs.
>
> I think it would be best to remove all uses of that compound term, and
> rewrite the explanations using only the current parameter names
> (context_item, path_expression, etc).
>
> But I wasn't sure and I haven't done any such changes in the attached.
>
> Perhaps I'll give it a try during the weekend.
>
>
>
Thanks for this. If you want to follow up that last sentence I will try
to commit a single fix early next week.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
SQL/JSON documentation JSON_TABLE
Hi,
Attached are a few small changes to the JSON_TABLE section in func.sgml.
The first two changes are simple typos.
Then there was this line:
context_item, path_expression [ AS json_path_name ] [ PASSING { value AS
varname } [, ...]]
those are the parameters to JSON_TABLE() so I changed that line to:
JSON_TABLE(context_item, path_expression [ AS json_path_name ] [ PASSING
{ value AS varname } [, ...]])
Some parts of the JSON_TABLE text strike me as opaque. For instance,
there are paragraphs that more than once use the term:
json_api_common_syntax
'json_api_common_syntax' is not explained. It turns out it's a relic
from Nikita's original docs. I dug up a 2018 patch where the term is
used as:
2018:
JSON_TABLE (
json_api_common_syntax [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
(etc...)
with explanation:
2018:
json_api_common_syntax:
The input data to query, the JSON path expression defining the
query, and an optional PASSING clause.
So that made sense then (input+jsonpath+params=api), but it doesn't now
fit as such in the current docs.
I think it would be best to remove all uses of that compound term, and
rewrite the explanations using only the current parameter names
(context_item, path_expression, etc).
But I wasn't sure and I haven't done any such changes in the attached.
Perhaps I'll give it a try during the weekend.
Erik Rijkers
--- ./doc/src/sgml/func.sgml.orig 2022-07-08 19:46:46.018505707 +0200
+++ ./doc/src/sgml/func.sgml 2022-07-08 20:47:35.488303254 +0200
@@ -18026,7 +18026,7 @@
or array, but if it is CONDITIONAL it will not be
applied to a single array or object. UNCONDITIONAL
is the default.
-If the result is a a scalar string, by default the value returned will have
+If the result is a scalar string, by default the value returned will have
surrounding quotes making it a valid JSON value. However, this behavior
is reversed if OMIT QUOTES is specified.
The ON ERROR and ON EMPTY
@@ -18097,7 +18097,7 @@
columns. Columns produced by NESTED PATHs at the
same level are considered to be siblings,
while a column produced by a NESTED PATH is
- considered to be a child of the column produced by and
+ considered to be a child of the column produced by a
NESTED PATH or row expression at a higher level.
Sibling columns are always joined first. Once they are processed,
the resulting rows are joined to the parent row.
@@ -18106,7 +18106,7 @@
- context_item, path_expression AS json_path_name PASSING { value AS varname } , ...
+ JSON_TABLE(context_item, path_expression AS json_path_name PASSING { value AS varname } , ...)
Re: SQL/JSON: documentation
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 signature.asc Description: PGP signature
Re: SQL/JSON: documentation
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-904886dea...@postgrespro.ru
[3]
https://www.postgresql.org/message-id/flat/132f26c4-dfc6-f8fd-4764-2cbf455a3...@postgrespro.ru
--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/biblio.sgml b/doc/src/sgml/biblio.sgml
index 4953024..f06305d 100644
--- a/doc/src/sgml/biblio.sgml
+++ b/doc/src/sgml/biblio.sgml
@@ -136,6 +136,17 @@
1988
+
+SQL Technical Report
+Part 6: SQL support for JavaScript Object
+ Notation (JSON)
+First Edition.
+
+http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip;>.
+
+2017.
+
+
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 112d962..20ef7df 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11285,26 +11285,661 @@ table2-mapping
- JSON Functions and Operators
+ JSON Functions, Operators, and Expressions
-
+
+ The functions, operators, and expressions described in this section
+ operate on JSON data:
+
+
+
+
+
+ SQL/JSON path expressions
+ (see ).
+
+
+
+
+ PostgreSQL-specific functions and operators for JSON
+ data types (see ).
+
+
+
+
+
+To learn more about the SQL/JSON standard, see
+. For details on JSON types
+supported in PostgreSQL,
+see .
+
+
+
+ SQL/JSON Path Expressions
+
+
+ SQL/JSON path expressions specify the items to be retrieved
+ from the JSON data, similar to XPath expressions used
+ for SQL access to XML. In PostgreSQL,
+ path expressions are implemented as the jsonpath
+ data type, described in .
+
+
+ JSON query functions and operators
+ pass the provided path expression to the path engine
+ for evaluation. If the expression matches the JSON data to be queried,
+ the corresponding SQL/JSON item is returned.
+ Path expressions are written in the SQL/JSON path language
+ and can also include arithmetic expressions and functions.
+ Query functions treat the provided expression as a
+ text string, so it must be enclosed in single quotes.
+
+
+
+ A path expression consists of a sequence of elements allowed
+ by the jsonpath data type.
+ The path expression is evaluated from left to right, but
+ you can use parentheses to change the order of operations.
+ If the evaluation is successful, an SQL/JSON sequence is produced,
+ and the evaluation result is returned to the JSON query function
+ that completes the specified computation.
+
+
+
+ To refer to the JSON data to be queried (the
+ context item), use the $ sign
+ in the path expression. It can be followed by one or more
+ accessor operators,
+ which go down the JSON structure level by level to retrieve the
+ content of context item. Each operator that follows deals with the
+ result of the previous evaluation step.
+
+
+
+ For example, suppose you have some JSON data from a GPS tracker that you
+ would like to parse, such as:
+
+{ "track" :
+ {
+"segments" : [
+ { "location": [ 47.763, 13.4034 ],
+"start time": "2018-10-14 10:05:14",
+"HR": 73
+ },
+ { "location": [ 47.706, 13.2635 ],
+"start time": "2018-10-14 10:39:21",
+"HR": 130
+ } ]
+ }
+}
+
+
+
+
+ To retrieve the available track segments, you need to use the
+ .key accessor
+ operator for all the preceding JSON objects:
+
+'$.track.segments'
+
+
+
+
+ If the item to retrieve is an element of an array, you have
+ to unnest this array using the [*] operator. For example,
+ the following path will return location coordinates for all
+ the available track segments:
+
+'$.track.segments[*].location'
+
+
+
+
+ To return the coordinates of the first segment only, you can
+ specify the
Re: SQL/JSON: documentation
> On Mon, Oct 1, 2018 at 2:24 PM Liudmila Mantrova > 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
Re: SQL/JSON: documentation
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
Re: SQL/JSON: documentation
Andrew Dunstan 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
Re: SQL/JSON: documentation
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 Dunstanhttps://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: SQL/JSON: documentation
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
Re: SQL/JSON: documentation
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
Re: SQL/JSON: documentation
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 typePG 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
(foo5)
(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
Re: SQL/JSON: documentation
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
