Thread: JSONPATH documentation
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
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?
"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
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, -10There 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 yieldthe 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 ifthe 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 nota 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 notdescribed 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/
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
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 shouldbe 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 wouldlike 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
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
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
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 availableso we can link to it? Perhaps there is some document or page you think would be a good reference read for peoplewho 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 ofthe 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 :DOn 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
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
Attachment
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