Re: JSON Functions and Operators Docs for v15
On 2022-05-19 Th 08:19, Justin Pryzby wrote: >> + same level are considerd to be siblings, > considered > >> +PostgreSQL specific functions detailed in > postgresql hyphen specific (as in the original) > > These would all be easier to read with commas: > > +expression is NULL an > +If WITH UNIQUE is specified the > +If the input is NULL an SQL NULL is returned. If the input is a > number > +If WITH UNIQUE is specified there must not > +is strict an error is generated if it yields no > items. > > There's a few instances of "space space" that could be condensed: > > + json_scalar functions, this needs to be either > json or > +which must be a SELECT query returning a single column. If Thanks, pushed now. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: JSON Functions and Operators Docs for v15
> + same level are considerd to be siblings, considered > +PostgreSQL specific functions detailed in postgresql hyphen specific (as in the original) These would all be easier to read with commas: +expression is NULL an +If WITH UNIQUE is specified the +If the input is NULL an SQL NULL is returned. If the input is a number +If WITH UNIQUE is specified there must not +is strict an error is generated if it yields no items. There's a few instances of "space space" that could be condensed: + json_scalar functions, this needs to be either json or +which must be a SELECT query returning a single column. If
Re: JSON Functions and Operators Docs for v15
On 2022-May-16, Andrew Dunstan wrote: > Thanks. I have incorporated all of these, added a result for the last > json_table example, and done some more wordsmithing around PLAN DEFAULT. For sure this is a big improvement, thanks. No longer do we have to refer to section 9.16.3.2.2.3 -- that's in table 9.53 now. I noticed that after applying it, the (some?) synopses end up partly typeset with regular typeface rather than fixed-width, which looks a bit odd. I think you need some tags around keywords and around the parameters to those; that's how overlay does it for example for its PLACING/FROM/FOR weird SQL bits. Thanks! -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/
Re: JSON Functions and Operators Docs for v15
Op 16-05-2022 om 20:53 schreef Andrew Dunstan: On 2022-05-16 Mo 13:52, Erik Rijkers wrote: - 2. This example in func.sgml says it gives 't' but on my instance it returns 'f'. Is the example correct? jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())') → t Yeah, it doesn't like the format of the timestamp literal. It works with "2015-08-01T12:00:0 -05". I'll fix the example in the next version. That doesn't work either, in my hands. It seems the offending chracteristic is the presence of the second space, before -05 cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: JSON Functions and Operators Docs for v15
On 2022-05-16 Mo 14:53, Andrew Dunstan wrote: > On 2022-05-16 Mo 13:52, Erik Rijkers wrote: >> Op 16-05-2022 om 16:49 schreef Andrew Dunstan: >> >>> [sqljson-dox-rework-2.patch] >> Two issues, derived from func.sgml: >> >> - >> 1. >> >> I noticed that some json functions, for instance json_object(), in >> their output insert unexpected spaces before the separator-colon: >> >> testdb=# select json_object('{a, 1, b, "def", c, 3.5}'); >> >> json_object >> --- >> {"a" : "1", "b" : "def", "c" : "3.5"} >> (1 row) >> >> instead of the expected >> {"a": "1", "b": "def", "c": "3.5"} >> >> Of course not outright wrong but wouldn't it make more sense to >> normalize such output? There is here no reason in the input to space >> the colon on both sides. >> >> Functions that yield this peculiarly spaced output are: >> json_object >> json_objectagg >> json_build_object >> > Well, yes, possibly, but don't think we're going to change the behavior > now, it might break things. > > >> - >> 2. >> >> This example in func.sgml says it gives 't' but on my instance it >> returns 'f'. Is the example correct? >> >> jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? >> (@.datetime() < "2015-08-02".datetime())') → t > > > Yeah, it doesn't like the format of the timestamp literal. It works with > "2015-08-01T12:00:0 -05". I'll fix the example in the next version. Or rather "2015-08-01T12:00:00-05" cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: JSON Functions and Operators Docs for v15
On 2022-05-16 Mo 13:52, Erik Rijkers wrote: > Op 16-05-2022 om 16:49 schreef Andrew Dunstan: > >> [sqljson-dox-rework-2.patch] > > Two issues, derived from func.sgml: > > - > 1. > > I noticed that some json functions, for instance json_object(), in > their output insert unexpected spaces before the separator-colon: > > testdb=# select json_object('{a, 1, b, "def", c, 3.5}'); > > json_object > --- > {"a" : "1", "b" : "def", "c" : "3.5"} > (1 row) > > instead of the expected > {"a": "1", "b": "def", "c": "3.5"} > > Of course not outright wrong but wouldn't it make more sense to > normalize such output? There is here no reason in the input to space > the colon on both sides. > > Functions that yield this peculiarly spaced output are: > json_object > json_objectagg > json_build_object > Well, yes, possibly, but don't think we're going to change the behavior now, it might break things. > - > 2. > > This example in func.sgml says it gives 't' but on my instance it > returns 'f'. Is the example correct? > > jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? > (@.datetime() < "2015-08-02".datetime())') → t Yeah, it doesn't like the format of the timestamp literal. It works with "2015-08-01T12:00:0 -05". I'll fix the example in the next version. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: JSON Functions and Operators Docs for v15
Op 16-05-2022 om 16:49 schreef Andrew Dunstan: [sqljson-dox-rework-2.patch] Two issues, derived from func.sgml: - 1. I noticed that some json functions, for instance json_object(), in their output insert unexpected spaces before the separator-colon: testdb=# select json_object('{a, 1, b, "def", c, 3.5}'); json_object --- {"a" : "1", "b" : "def", "c" : "3.5"} (1 row) instead of the expected {"a": "1", "b": "def", "c": "3.5"} Of course not outright wrong but wouldn't it make more sense to normalize such output? There is here no reason in the input to space the colon on both sides. Functions that yield this peculiarly spaced output are: json_object json_objectagg json_build_object - 2. This example in func.sgml says it gives 't' but on my instance it returns 'f'. Is the example correct? jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())') → t Thanks, Erik andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: JSON Functions and Operators Docs for v15
Not done yet but here's where I'm at. If I'm on the wrong track or missing things that should be done please let me know. [sqljson-dox-rework.patch] Here are a few errors/typos/improvements. I've added (=copied from the old docs) the CREATE TABLE for the my_films table so that the more complicated json_table examples can be run easily. Erik Rijkers -- Andrew Dunstan EDB: https://www.enterprisedb.com--- doc/src/sgml/func.sgml.orig 2022-05-14 06:32:28.564537299 +0200 +++ doc/src/sgml/func.sgml 2022-05-14 08:10:05.313313154 +0200 @@ -16287,7 +16287,7 @@ jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null) -[{"f1":null,"f2":null},2,null,3] +[{"f1": null, "f2": null}, 2, null, 3] jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target') @@ -17682,7 +17682,7 @@ object keys. -json('{"a" 123, "b":[true,"foo"], "a":"bar"}') +json('{"a":123, "b":[true,"foo"], "a":"bar"}') {"a":123, "b":[true,"foo"], "a":"bar"} @@ -17959,7 +17959,7 @@ json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR) -ERROR: Invalid SQL/JSON subscript +ERROR: jsonpath array subscript is out of bounds @@ -17990,11 +17990,11 @@ of the ON EMPTY clause. -json_value('"123.45"', '$' RETURNING float) +json_value('"123.45"'::jsonb, '$' RETURNING float) 123.45 -json_value('"03:04 2015-02-01"', '$.datetime("HH24:MI -MM-DD")' RETURNING date) +json_value('"03:04 2015-02-01"'::jsonb, '$.datetime("HH24:MI -MM-DD")' RETURNING date) 2015-02-01 @@ -18111,7 +18111,7 @@ The input data to query, the JSON path expression defining the query, - and an optional PASSING clause, which can privide data + and an optional PASSING clause, which can provide data values to the path_expression. The result of the input data evaluation is called the row pattern. The row @@ -18409,6 +18409,31 @@ Examples + In these examples the following small table storing some JSON data will be used: + +CREATE TABLE my_films ( js jsonb ); + +INSERT INTO my_films VALUES ( +'{ "favorites" : [ + { "kind" : "comedy", "films" : [ + { "title" : "Bananas", + "director" : "Woody Allen"}, + { "title" : "The Dinner Game", + "director" : "Francis Veber" } ] }, + { "kind" : "horror", "films" : [ + { "title" : "Psycho", + "director" : "Alfred Hitchcock" } ] }, + { "kind" : "thriller", "films" : [ + { "title" : "Vertigo", + "director" : "Alfred Hitchcock" } ] }, + { "kind" : "drama", "films" : [ + { "title" : "Yojimbo", + "director" : "Akira Kurosawa" } ] } + ] }'); + + + + Query the my_films table holding some JSON data about the films and create a view that distributes the film genre, title, and director between separate columns: @@ -18427,7 +18452,7 @@ 1 | comedy | Bananas | Woody Allen 1 | comedy | The Dinner Game | Francis Veber 2 | horror | Psycho | Alfred Hitchcock - 3 | thriller | Vertigo | Hitchcock + 3 | thriller | Vertigo | Alfred Hitchcock 4 | drama| Yojimbo | Akira Kurosawa (5 rows)
Re: JSON Functions and Operators Docs for v15
On 2022-05-04 We 15:14, Andrew Dunstan wrote: > On 2022-05-04 We 11:39, Tom Lane wrote: >> "David G. Johnston" writes: >>> Is there a thread I'm not finding where the upcoming JSON function >>> documentation is being made reasonably usable after doubling its size with >>> all the new JSON Table features that we've added? If nothing else, the >>> table of contents at the top of the page needs to be greatly expanded to >>> make seeing and navigating to all that is available a possibility. >> The entire structure of that text needs to be rethought, IMO, as it >> has been written with precisely no concern for fitting into our >> hard-won structure for func.sgml. Andrew muttered something about >> rewriting it awhile ago, but I don't know what progress he's made. >> > Yes, I've been clearing the decks a bit, but I'm working on it now, > should have something within the next week. Running slightly long on this. Will definitely post a patch by COB Friday. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: JSON Functions and Operators Docs for v15
On 2022-05-04 We 11:39, Tom Lane wrote: > "David G. Johnston" writes: >> Is there a thread I'm not finding where the upcoming JSON function >> documentation is being made reasonably usable after doubling its size with >> all the new JSON Table features that we've added? If nothing else, the >> table of contents at the top of the page needs to be greatly expanded to >> make seeing and navigating to all that is available a possibility. > The entire structure of that text needs to be rethought, IMO, as it > has been written with precisely no concern for fitting into our > hard-won structure for func.sgml. Andrew muttered something about > rewriting it awhile ago, but I don't know what progress he's made. > Yes, I've been clearing the decks a bit, but I'm working on it now, should have something within the next week. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Re: JSON Functions and Operators Docs for v15
On Wed, May 4, 2022 at 8:39 AM Tom Lane wrote: > "David G. Johnston" writes: > > Is there a thread I'm not finding where the upcoming JSON function > > documentation is being made reasonably usable after doubling its size > with > > all the new JSON Table features that we've added? If nothing else, the > > table of contents at the top of the page needs to be greatly expanded to > > make seeing and navigating to all that is available a possibility. > > The entire structure of that text needs to be rethought, IMO, as it > has been written with precisely no concern for fitting into our > hard-won structure for func.sgml. Andrew muttered something about > rewriting it awhile ago, but I don't know what progress he's made. > > I suppose regardless of the answer, or which thread is used for the patch, the question at hand is whether this is problematic enough to warrant an open item. I would lean toward yes, we can decide how much reworking is considered sufficient to clear the open item separately. David J.
Re: JSON Functions and Operators Docs for v15
On Wed, May 04, 2022 at 08:32:51AM -0700, David G. Johnston wrote: > Hey, > > Is there a thread I'm not finding where the upcoming JSON function > documentation is being made reasonably usable after doubling its size with > all the new JSON Table features that we've added? If nothing else, the > table of contents at the top of the page needs to be greatly expanded to > make seeing and navigating to all that is available a possibility. https://www.postgresql.org/message-id/20220411160905.gh26...@telsasoft.com
Re: JSON Functions and Operators Docs for v15
"David G. Johnston" writes: > Is there a thread I'm not finding where the upcoming JSON function > documentation is being made reasonably usable after doubling its size with > all the new JSON Table features that we've added? If nothing else, the > table of contents at the top of the page needs to be greatly expanded to > make seeing and navigating to all that is available a possibility. The entire structure of that text needs to be rethought, IMO, as it has been written with precisely no concern for fitting into our hard-won structure for func.sgml. Andrew muttered something about rewriting it awhile ago, but I don't know what progress he's made. regards, tom lane
JSON Functions and Operators Docs for v15
Hey, Is there a thread I'm not finding where the upcoming JSON function documentation is being made reasonably usable after doubling its size with all the new JSON Table features that we've added? If nothing else, the table of contents at the top of the page needs to be greatly expanded to make seeing and navigating to all that is available a possibility. David J.