Re: JSONPATH documentation

Hi!

On Sun, Sep 22, 2019 at 9:18 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)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