Horton

Thread

Commits

  1. Rework SQL/JSON documentation

  1. JSON Functions and Operators Docs for v15

    David G. Johnston <david.g.johnston@gmail.com> — 2022-05-04T15:32:51Z

    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.

  2. Re: JSON Functions and Operators Docs for v15

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-05-04T15:39:27Z

    "David G. Johnston" <david.g.johnston@gmail.com> 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

  3. Re: JSON Functions and Operators Docs for v15

    Justin Pryzby <pryzby@telsasoft.com> — 2022-05-04T15:42:48Z

    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.GH26620@telsasoft.com

  4. Re: JSON Functions and Operators Docs for v15

    David G. Johnston <david.g.johnston@gmail.com> — 2022-05-04T15:44:01Z

    On Wed, May 4, 2022 at 8:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> 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.

  5. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-04T19:14:58Z

    On 2022-05-04 We 11:39, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> 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

  6. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-10T21:45:53Z

    On 2022-05-04 We 15:14, Andrew Dunstan wrote:
> On 2022-05-04 We 11:39, Tom Lane wrote:
>> "David G. Johnston" <david.g.johnston@gmail.com> 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

  7. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-14T01:37:54Z

    On 2022-05-10 Tu 17:45, Andrew Dunstan wrote:
> On 2022-05-04 We 15:14, Andrew Dunstan wrote:
>> On 2022-05-04 We 11:39, Tom Lane wrote:
>>> "David G. Johnston" <david.g.johnston@gmail.com> 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.
>
>

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.

I got rid of all the sub-sub-sub-sections, and put most of the functions
into tables like most other function sections. I added indexterm entries
liberally, and removed a deal of repetitive text. I put json_table in
its own subsection, because it's big enough and important enough, I
think. I reworked some of its docco, particularly around joining of
sibling rows and PLAN DEFAULT, but there's a deal of work still to do to
whip it into shape, which I will continue to do over the weekend.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  8. Re: JSON Functions and Operators Docs for v15

    Erik Rijkers <er@xs4all.nl> — 2022-05-14T06:17:05Z

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

  9. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-16T14:49:19Z

    On 2022-05-14 Sa 02:17, Erik Rijkers wrote:
>>
>> 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.
>
>
>

Thanks. I have incorporated all of these, added a result for the last
json_table example, and done some more wordsmithing around PLAN DEFAULT.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  10. Re: JSON Functions and Operators Docs for v15

    Erik Rijkers <er@xs4all.nl> — 2022-05-16T17:52:43Z

    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

  11. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-16T18:53:55Z

    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

  12. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-16T19:02:27Z

    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

  13. Re: JSON Functions and Operators Docs for v15

    Erik Rijkers <er@xs4all.nl> — 2022-05-16T19:06:59Z

    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
>

  14. Re: JSON Functions and Operators Docs for v15

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2022-05-16T19:33:28Z

    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 <literal> tags around keywords and
<parameter> around the parameters to those; that's how
<function>overlay</function> does it for example for its
PLACING/FROM/FOR weird SQL bits.

Thanks!

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

  15. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-19T01:30:29Z

    On 2022-05-16 Mo 15:33, Alvaro Herrera wrote:
> 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 <literal> tags around keywords and
> <parameter> around the parameters to those; that's how
> <function>overlay</function> does it for example for its
> PLACING/FROM/FOR weird SQL bits.
>
> Thanks!



Thanks for reviewing. Here's an updated version that I hope addresses
your comments. I'm going on vacation for 10 days tomorrow, but I'm
hoping to commit this before I leave.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  16. Re: JSON Functions and Operators Docs for v15

    Justin Pryzby <pryzby@telsasoft.com> — 2022-05-19T12:19:05Z

    > +   same level are considerd to be <firstterm>siblings</firstterm>,

considered

> +    <productname>PostgreSQL</productname> specific functions detailed in

postgresql hyphen specific (as in the original)

These would all be easier to read with commas:

+        <parameter>expression</parameter> is NULL an
+        If <literal>WITH UNIQUE</literal> is specified the
+        If the input is NULL an SQL NULL is returned. If the input is a number
+        If <literal>WITH UNIQUE</literal> is specified there must not
+        is <literal>strict</literal> an error is generated if it yields no items.

There's a few instances of "space space" that could be condensed:

+   <function>json_scalar</function> functions, this needs to be  either <type>json</type> or
+        which  must be a SELECT query returning a single column. If

  17. Re: JSON Functions and Operators Docs for v15

    Andrew Dunstan <andrew@dunslane.net> — 2022-05-19T14:50:08Z

    On 2022-05-19 Th 08:19, Justin Pryzby wrote:
>> +   same level are considerd to be <firstterm>siblings</firstterm>,
> considered
>
>> +    <productname>PostgreSQL</productname> specific functions detailed in
> postgresql hyphen specific (as in the original)
>
> These would all be easier to read with commas:
>
> +        <parameter>expression</parameter> is NULL an
> +        If <literal>WITH UNIQUE</literal> is specified the
> +        If the input is NULL an SQL NULL is returned. If the input is a number
> +        If <literal>WITH UNIQUE</literal> is specified there must not
> +        is <literal>strict</literal> an error is generated if it yields no items.
>
> There's a few instances of "space space" that could be condensed:
>
> +   <function>json_scalar</function> functions, this needs to be  either <type>json</type> or
> +        which  must be a SELECT query returning a single column. If



Thanks, pushed now.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com