JSON Functions and Operators Docs for v15

"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

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.

/messages/by-id/20220411160905.GH26620@telsasoft.com

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.

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

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

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

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

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

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

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

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

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

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/

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

+ 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

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