Horton

Thread

Commits

  1. doc: Update a confusing sentence about SQL/JSON

  2. doc: Reorder JSON functions documentation

  1. JSONPATH documentation

    Jeff Janes <jeff.janes@gmail.com> — 2019-09-22T18:18:04Z

    I find the documentation in
https://www.postgresql.org/docs/12/functions-json.html very confusing.

In table 9.44 take the first entry,

Example JSON
 {"x": [2.85, -14.7, -9.4]}

Example Query
  + $.x.floor()

Result
2, -15, -10

There are no end to end examples here. How do I apply the example query to
the example json to obtain the given result?

Table 9.47 only gives two operators which apply a jsonpath to a json(b)
object: @? and @@; and neither one of those yield the indicated result from
the first line in 9.44. What does?

Also, I can't really figure out what the descriptions of @? and @@ mean.
Does @? return true if an item exists, even if the value of that item is
false, while @@ returns the truth value of the existing item?

https://www.postgresql.org/docs/12/datatype-json.html#DATATYPE-JSONPATH

"The SQL/JSON path language is fully integrated into the SQL engine". What
does that mean?  If it were only partially integrated, what would that
mean?  Is this providing me with any useful information?  Is this just
saying that this is not a contrib extension module?

What is the difference between "SQL/JSON Path Operators And Methods" and
and "jsonpath Accessors" and why are they not described in the same place,
or at least nearby each other?

Cheers,

Jeff

  2. Re: JSONPATH documentation

    Jeff Janes <jeff.janes@gmail.com> — 2019-09-22T20:36:28Z

    On Sun, Sep 22, 2019 at 2:18 PM Jeff Janes <jeff.janes@gmail.com> wrote:

> I find the documentation in
> https://www.postgresql.org/docs/12/functions-json.html very confusing.
>
> In table 9.44 take the first entry,
>
> Example JSON
>  {"x": [2.85, -14.7, -9.4]}
>
> Example Query
>   + $.x.floor()
>
> Result
> 2, -15, -10
>
> There are no end to end examples here. How do I apply the example query to
> the example json to obtain the given result?
>

OK, never mind here.  After digging in the regression tests, I did find
jsonb_path_query and friends, and they are in the docs with examples in
table 9.49.  I don't know how I overlooked that in the first place, I guess
I was fixated on operators.  Or maybe by the time I was down in those
functions, I thought I had cycled back up and was looking at 9.44 again.
But I think it would make sense to move the description of jsonpath to its
own page.  It is confusing to have operators within the jsonpath language,
and operators which apply to jsonpath "from the outside", together in the
same page.

Cheers,

Jeff

>

  3. Re: JSONPATH documentation

    Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-22T20:56:27Z

    Hi!

On Sun, Sep 22, 2019 at 9:18 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> I find the documentation in  https://www.postgresql.org/docs/12/functions-json.html very confusing.
>
> In table 9.44 take the first entry,
>
> Example JSON
>  {"x": [2.85, -14.7, -9.4]}
>
> Example Query
>   + $.x.floor()
>
> Result
> 2, -15, -10
>
> There are no end to end examples here. How do I apply the example query to the example json to obtain the given result?

Yes, I agree this looks unclear.  I can propose two possible solutions.
1) Include full queries into the table.  For instance, it could be
"SELECT jsonb_path_query_array('{"x": [2.85, -14.7, -9.4]}', '+
$.x.floor()');".  Or at least full SQL expressions, e.g.
"jsonb_path_query_array('{"x": [2.85, -14.7, -9.4]}', '+
$.x.floor()')".
2) Add a note clarifying which functions use to run the examples.

What do you think?

> Table 9.47 only gives two operators which apply a jsonpath to a json(b) object: @? and @@; and neither one of those yield the indicated result from the first line in 9.44. What does?

Operators don't produce these results.  These results may be produced
by jsonb_path_query() or jsonb_path_query_array() functions described
in table 9.49.

> Also, I can't really figure out what the descriptions of @? and @@ mean.  Does @? return true if an item exists, even if the value of that item is false, while @@ returns the truth value of the existing item?

I see @? and @@ are lacking of examples.  And description given in the
table is a bit vague.

@? checks if jsonpath returns at least of item.

# SELECT '{"x": [2.85, -14.7, -9.4]}' @? '$.x[*] ? (@ > 2)';
 ?column?
----------
 t

# SELECT '{"x": [2.85, -14.7, -9.4]}' @? '$.x[*] ? (@ > 3)';
 ?column?
----------
 f

@@ checks if first item returned by jsonpath is true.

# SELECT '{"x": [2.85, -14.7, -9.4]}' @@ '$.x.size() == 3';
 ?column?
----------
 f

# SELECT '{"x": [2.85, -14.7, -9.4]}' @@ '$.x.size() == 4';
 ?column?
----------
 f

> https://www.postgresql.org/docs/12/datatype-json.html#DATATYPE-JSONPATH
>
> "The SQL/JSON path language is fully integrated into the SQL engine". What does that mean?  If it were only partially integrated, what would that mean?  Is this providing me with any useful information?  Is this just saying that this is not a contrib extension module?

I guess, this sentence comes from uncommitted patch, which implements
SQL/JSON clauses.  I see that now we only can use jsonpath in
functions and operator.  So, we can't say it's fully integrated.

> What is the difference between "SQL/JSON Path Operators And Methods" and  and "jsonpath Accessors" and why are they not described in the same place, or at least nearby each other?

Accessors are used to access parts of json objects/arrays, while
operators manipulates accessed parts.  This terminology comes from SQL
standard.  In principle we could call accessors and operators the same
name, but we follow standard terminology.

Currently description of jsonpath is divided between datatypes section
and functions and operators section.  And yes, this looks cumbersome.
I think we should move the whole description to the one section.
Probably we should move jsonpath description to datatypes section
(assuming jsonpath is a datatype) leaving functions and operators
section with just SQL-level functions and operators.  What do you
think?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

  4. Re: JSONPATH documentation

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-09-22T22:03:32Z

    Alexander Korotkov <a.korotkov@postgrespro.ru> writes:
> On Sun, Sep 22, 2019 at 9:18 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> Currently description of jsonpath is divided between datatypes section
> and functions and operators section.  And yes, this looks cumbersome.

Agreed, but ...

> I think we should move the whole description to the one section.
> Probably we should move jsonpath description to datatypes section
> (assuming jsonpath is a datatype) leaving functions and operators
> section with just SQL-level functions and operators.  What do you
> think?

... I don't think that's an improvement.  We don't document detailed
behavior of a datatype's functions in datatype.sgml, and this seems
like it would be contrary to that layout.  If anything, I'd merge
the other way, with only a very minimal description of jsonpath
(perhaps none?) in datatype.sgml.

While we're whining about this, I find it very off-putting that
the jsonpath stuff was inserted in the JSON functions section
ahead of the actual JSON functions.  I think it should have
gone after them, because it feels like a barely-related interjection
as it stands.  Maybe there's even a case that it should be
its own <sect1>, after the "functions-json" section.

			regards, tom lane

  5. Re: JSONPATH documentation

    Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-22T23:03:14Z

    On Mon, Sep 23, 2019 at 1:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexander Korotkov <a.korotkov@postgrespro.ru> writes:
> > On Sun, Sep 22, 2019 at 9:18 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> > Currently description of jsonpath is divided between datatypes section
> > and functions and operators section.  And yes, this looks cumbersome.
>
> Agreed, but ...
>
> > I think we should move the whole description to the one section.
> > Probably we should move jsonpath description to datatypes section
> > (assuming jsonpath is a datatype) leaving functions and operators
> > section with just SQL-level functions and operators.  What do you
> > think?
>
> ... I don't think that's an improvement.  We don't document detailed
> behavior of a datatype's functions in datatype.sgml, and this seems
> like it would be contrary to that layout.  If anything, I'd merge
> the other way, with only a very minimal description of jsonpath
> (perhaps none?) in datatype.sgml.
>
> While we're whining about this, I find it very off-putting that
> the jsonpath stuff was inserted in the JSON functions section
> ahead of the actual JSON functions.  I think it should have
> gone after them, because it feels like a barely-related interjection
> as it stands.  Maybe there's even a case that it should be
> its own <sect1>, after the "functions-json" section.

Yes, it think moving jsonpath description to own <sect1> is a good
idea.  But I still think we should have complete jsonpath description
in the single place.  What about joining jsonpath description from
both datatypes section and functions and operators section into this
<sect1>, leaving datatypes section with something very brief?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

  6. Re: JSONPATH documentation

    Steven Pousty <steve.pousty@gmail.com> — 2019-09-23T16:52:24Z

    JSON Containment, JSONPath, and Transforms are means to work with JSONB but
not the actual datatype itself. Doc should be split into
1) Data type - how do declare, indexing, considerations when using it...
2) Ways to work with the data type - functions, containment, JSONPath...

These can be separate pages or on the same page but they need to be
logically and physically separated

There should also be a link to some of the original JSONPath spec
https://goessner.net/articles/JsonPath/

Thank you so much for putting so much work into the documentation! Please
let me know if there are others way you would like to me help with the doc.

On Sun, Sep 22, 2019 at 4:03 PM Alexander Korotkov <
a.korotkov@postgrespro.ru> wrote:

> On Mon, Sep 23, 2019 at 1:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Alexander Korotkov <a.korotkov@postgrespro.ru> writes:
> > > On Sun, Sep 22, 2019 at 9:18 PM Jeff Janes <jeff.janes@gmail.com>
> wrote:
> > > Currently description of jsonpath is divided between datatypes section
> > > and functions and operators section.  And yes, this looks cumbersome.
> >
> > Agreed, but ...
> >
> > > I think we should move the whole description to the one section.
> > > Probably we should move jsonpath description to datatypes section
> > > (assuming jsonpath is a datatype) leaving functions and operators
> > > section with just SQL-level functions and operators.  What do you
> > > think?
> >
> > ... I don't think that's an improvement.  We don't document detailed
> > behavior of a datatype's functions in datatype.sgml, and this seems
> > like it would be contrary to that layout.  If anything, I'd merge
> > the other way, with only a very minimal description of jsonpath
> > (perhaps none?) in datatype.sgml.
> >
> > While we're whining about this, I find it very off-putting that
> > the jsonpath stuff was inserted in the JSON functions section
> > ahead of the actual JSON functions.  I think it should have
> > gone after them, because it feels like a barely-related interjection
> > as it stands.  Maybe there's even a case that it should be
> > its own <sect1>, after the "functions-json" section.
>
> Yes, it think moving jsonpath description to own <sect1> is a good
> idea.  But I still think we should have complete jsonpath description
> in the single place.  What about joining jsonpath description from
> both datatypes section and functions and operators section into this
> <sect1>, leaving datatypes section with something very brief?
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>

  7. Re: JSONPATH documentation

    Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-23T18:06:55Z

    On Mon, Sep 23, 2019 at 7:52 PM Steven Pousty <steve.pousty@gmail.com> wrote:
> JSON Containment, JSONPath, and Transforms are means to work with JSONB but not the actual datatype itself. Doc should be split into
> 1) Data type - how do declare, indexing, considerations when using it...
> 2) Ways to work with the data type - functions, containment, JSONPath...
>
> These can be separate pages or on the same page but they need to be logically and physically separated

According to your proposal, where jsonpath functions, operators and
accessors should be described in?  On the one hand jsonpath functions
etc. are part of jsonpath datatype.  On the other hand it's functions
we apply to jsonb documents.

> There should also be a link to some of the original JSONPath spec
> https://goessner.net/articles/JsonPath/

We implement JSONPath according to SQL Standard 2016.  Your link
provides earlier attempt to implement jsonpath.  It's similar, but
some examples don't follow standard (and don't work in our
implementation).  For instance '$.store.book[(@.length-1)].title'
should be written as '$.store.book[last - 1] .title'.

> Thank you so much for putting so much work into the documentation! Please let me know if there are others way you would like to me help with the doc.

Thank you!  My main point is that we should put description of
jsonpath into single place.  But we need to reach consensus on what
this place should be.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

  8. Re: JSONPATH documentation

    Steven Pousty <steve.pousty@gmail.com> — 2019-09-23T19:10:28Z

    Hey there:
Thanks for the education on the path spec. Too bad it is in a zip doc - do
you know of a place where it is publicly available so we can link to it?
Perhaps there is some document or page you think would be a good reference
read for people who want to understand more?
https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip

I am uncertain why JSONPath is considered part of the datatype any more so
than string functions are considered part of the character datatype
https://www.postgresql.org/docs/11/functions-string.html


On Mon, Sep 23, 2019 at 11:07 AM Alexander Korotkov <
a.korotkov@postgrespro.ru> wrote:

> On Mon, Sep 23, 2019 at 7:52 PM Steven Pousty <steve.pousty@gmail.com>
> wrote:
> > JSON Containment, JSONPath, and Transforms are means to work with JSONB
> but not the actual datatype itself. Doc should be split into
> > 1) Data type - how do declare, indexing, considerations when using it...
> > 2) Ways to work with the data type - functions, containment, JSONPath...
> >
> > These can be separate pages or on the same page but they need to be
> logically and physically separated
>
> According to your proposal, where jsonpath functions, operators and
> accessors should be described in?  On the one hand jsonpath functions
> etc. are part of jsonpath datatype.  On the other hand it's functions
> we apply to jsonb documents.
>
> > There should also be a link to some of the original JSONPath spec
> > https://goessner.net/articles/JsonPath/
>
> We implement JSONPath according to SQL Standard 2016.  Your link
> provides earlier attempt to implement jsonpath.  It's similar, but
> some examples don't follow standard (and don't work in our
> implementation).  For instance '$.store.book[(@.length-1)].title'
> should be written as '$.store.book[last - 1] .title'.
>
> > Thank you so much for putting so much work into the documentation!
> Please let me know if there are others way you would like to me help with
> the doc.
>
> Thank you!  My main point is that we should put description of
> jsonpath into single place.  But we need to reach consensus on what
> this place should be.
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>

  9. Re: JSONPATH documentation

    Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-23T19:29:02Z

    Hi!

On Mon, Sep 23, 2019 at 10:10 PM Steven Pousty <steve.pousty@gmail.com> wrote:
> Thanks for the education on the path spec. Too bad it is in a zip doc - do you know of a place where it is publicly available so we can link to it? Perhaps there is some document or page you think would be a good reference read for people who want to understand more?
> https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip

Yes, this link looks good to me.  It's technical report, not standard
itself.  So, it may have some little divergences.  But it seems to be
the best free resource available, assuming standard itself isn't free.

> I am uncertain why JSONPath is considered part of the datatype any more so than string functions are considered part of the character datatype
> https://www.postgresql.org/docs/11/functions-string.html

Let me clarify my thoughts.  SQL-level functions jsonb_path_*() (table
9.49) are clearly not part of jsonpath datatype.  But jsonpath
accessors (table 8.25), functions (table 9.44) and operators (table
9.45) are used inside jsonpath value.  So, technically they are parts
of jsonpath datatype.

P.S.  We don't use top posting in mailing lists.  Please, use bottom
posting.  See https://en.wikipedia.org/wiki/Posting_style#Top-posting
for details.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

  10. Re: JSONPATH documentation

    Steven Pousty <steve.pousty@gmail.com> — 2019-09-23T19:52:21Z

    Privet :D


On Mon, Sep 23, 2019 at 12:29 PM Alexander Korotkov <
a.korotkov@postgrespro.ru> wrote:

> Hi!
>
> On Mon, Sep 23, 2019 at 10:10 PM Steven Pousty <steve.pousty@gmail.com>
> wrote:
> >
> https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
>
> Yes, this link looks good to me.  It's technical report, not standard
> itself.  So, it may have some little divergences.  But it seems to be
> the best free resource available, assuming standard itself isn't free.
>
> Works for me if we can't find something better


> > I am uncertain why JSONPath is considered part of the datatype any more
> so than string functions are considered part of the character datatype
> > https://www.postgresql.org/docs/11/functions-string.html
>
> Let me clarify my thoughts.  SQL-level functions jsonb_path_*() (table
> 9.49) are clearly not part of jsonpath datatype.  But jsonpath
> accessors (table 8.25), functions (table 9.44) and operators (table
> 9.45) are used inside jsonpath value.  So, technically they are parts
> of jsonpath datatype.
>
>
Yes but the only time I would use those  8.25, 9.44, and 9.45 is to just
create a jsonpath whose main purpose is to query or filter JSONB.
As a continued analogy, I think we rightly do not discuss anything but
creating and considerations when using character fields:
https://www.postgresql.org/docs/11/datatype-character.html

And then we have a separate page that talk about all the ways you can
manipulate and filter character fields.

My feeling is that JSONPath is only included as a way to work with JSONB,
not as requirement of JSONB. Therefore JSONPath documentation belongs with
all the other ways we work with JSONB, not as part of the datatype
definition.
JSONPath is important and complicated enough that it may warrant its own
page, just not in the same page where we define JSON(B)



> P.S.  We don't use top posting in mailing lists.  Please, use bottom
> posting.  See https://en.wikipedia.org/wiki/Posting_style#Top-posting
> for details.
>
>
Thanks for the very KIND etiquette correction -  I really appreciate you
not flaming me.

Thanks
Steve

  11. Re: JSONPATH documentation

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-09-24T21:08:25Z

    On 2019-09-23 00:03, Tom Lane wrote:
> While we're whining about this, I find it very off-putting that
> the jsonpath stuff was inserted in the JSON functions section
> ahead of the actual JSON functions.  I think it should have
> gone after them, because it feels like a barely-related interjection
> as it stands.  Maybe there's even a case that it should be
> its own <sect1>, after the "functions-json" section.

I'd just switch the sect2's around.

That would be similar to how the documentation of regular expressions is
laid out: functions first, then details of the contained mini-language.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  12. Re: JSONPATH documentation

    Liudmila Mantrova <l.mantrova@postgrespro.ru> — 2019-09-25T14:46:08Z

    On 9/25/19 12:08 AM, Peter Eisentraut wrote:
> On 2019-09-23 00:03, Tom Lane wrote:
>> While we're whining about this, I find it very off-putting that
>> the jsonpath stuff was inserted in the JSON functions section
>> ahead of the actual JSON functions.  I think it should have
>> gone after them, because it feels like a barely-related interjection
>> as it stands.  Maybe there's even a case that it should be
>> its own <sect1>, after the "functions-json" section.
> I'd just switch the sect2's around.

As more SQL/JSON functionality gets added, I believe a separate sect1 is 
likely to be more justified. However, for v12 I'd vote for moving sect2 
down. The patch is attached, it also fixes the ambiguous sentence that 
has raised questions in this thread.

-- 
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

  13. Re: JSONPATH documentation

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-09-26T14:38:44Z

    On 2019-09-25 16:46, Liudmila Mantrova wrote:
> On 9/25/19 12:08 AM, Peter Eisentraut wrote:
>> On 2019-09-23 00:03, Tom Lane wrote:
>>> While we're whining about this, I find it very off-putting that
>>> the jsonpath stuff was inserted in the JSON functions section
>>> ahead of the actual JSON functions.  I think it should have
>>> gone after them, because it feels like a barely-related interjection
>>> as it stands.  Maybe there's even a case that it should be
>>> its own <sect1>, after the "functions-json" section.
>> I'd just switch the sect2's around.
> 
> As more SQL/JSON functionality gets added, I believe a separate sect1 is 
> likely to be more justified. However, for v12 I'd vote for moving sect2 
> down. The patch is attached, it also fixes the ambiguous sentence that 
> has raised questions in this thread.

committed, thanks

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services