Обсуждение: 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.dunstan@2ndquadrant.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.mantrova@postgrespro.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@postgrespro.ru [3] https://www.postgresql.org/message-id/flat/132f26c4-dfc6-f8fd-4764-2cbf455a3aec@postgrespro.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