JSONPATH documentation

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

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

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

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

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

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:

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

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

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

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

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