Обсуждение: jsonpath: Missing Binary Execution Path?
Hackers,
Another apparent inconsistency I’ve noticed in jsonpath queries is the treatment of the && and || operators: They can’t
operateon scalar functions, only on other expressions. Some examples:
david=# select jsonb_path_query('true', '$ && $');
ERROR: syntax error at or near "&&" of jsonpath input
LINE 1: select jsonb_path_query('true', '$ && $');
^
david=# select jsonb_path_query('true', '$.boolean() && $.boolean()');
ERROR: syntax error at or near "&&" of jsonpath input
LINE 1: select jsonb_path_query('true', '$.boolean() && $.boolean()'...
^
The only place I’ve seen them work is inside filters with binary or unary operands:
jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)');
jsonb_path_query
------------------
3
It doesn’t even work with boolean methods!
david=# select jsonb_path_query('[1, 3, 7]', '$[*] ? (@.boolean() && @.boolean())');
ERROR: syntax error at or near "&&" of jsonpath input
LINE 1: select jsonb_path_query('[1, 3, 7]', '$[*] ? (@.boolean() &&...
^
Other binary operators work just fine in these sorts of contexts:
david=# select jsonb_path_query('1', '$ >= 1');
jsonb_path_query
------------------
true
(1 row)
david=# select jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1)');
jsonb_path_query
------------------
3
7
(2 rows)
Should && and || not also work on scalar operands?
Best,
David
On Jun 13, 2024, at 11:32, David E. Wheeler <david@justatheory.com> wrote:
> Should && and || not also work on scalar operands?
I see the same issue for unary !, too:
david=# select jsonb_path_query('true', '!$');
ERROR: syntax error at or near "$" of jsonpath input
LINE 1: select jsonb_path_query('true', '!$');
^
david=# select jsonb_path_query('[1, 3, 7]', '$[*] ? (!true)');
ERROR: syntax error at end of jsonpath input
LINE 1: select jsonb_path_query('[1, 3, 7]', '$[*] ? (!true)');
^
david=# select jsonb_path_query('[1, 3, 7]', '$[*] ? (!@.boolean())');
ERROR: syntax error at or near "@" of jsonpath input
LINE 1: select jsonb_path_query('[1, 3, 7]', '$[*] ? (!@.boolean())'...
^
Best,
David
On 2024-06-13 Th 11:37, David E. Wheeler wrote:
On Jun 13, 2024, at 11:32, David E. Wheeler <david@justatheory.com> wrote:Should && and || not also work on scalar operands?I see the same issue for unary !, too:
What does the spec say about these? What do other implementations do?
cheers
andrew
-- Andrew Dunstan EDB: https://www.enterprisedb.com
On Jun 13, 2024, at 3:33 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > What does the spec say about these? What do other implementations do? Paging Mr. Eisentraut! :-) D
On 06/13/24 16:43, David E. Wheeler wrote:
> Paging Mr. Eisentraut!
I'm not Mr. Eisentraut, but I have at last talked my way into some
access to the standard, so ...
Note 487 emphasizes that JSON path predicates "are not expressions;
instead they form a separate language that can only be invoked within
a <JSON filter expression>".
The only operators usable in a general expression (that is, a
<JSON path wff> are binary + - and binary * / % and unary + -
over a <JSON accessor expression>.
Inside a filter, you get to use a <JSON path predicate>. That's where
you can use ! and && and ||. But ! can only be applied to a
<JSON delimited predicate>: either a <JSON exists path predicate>,
or any other <JSON path predicate> wrapped in parentheses.
On 06/13/24 11:32, David E. Wheeler wrote:
> david=# select jsonb_path_query('true', '$ && $');
> david=# select jsonb_path_query('true', '$.boolean() && $.boolean()');
Those don't work because, as you recognized, they're not inside filters.
> david=# select jsonb_path_query('[1, 3, 7]', '$[*] ? (@.boolean() && @.boolean())');
That doesn't work because the operands of && or || must have the grammatical
form of predicates; it's not enough that they be expressions of boolean
type. '$[*] ? (@.boolean() == true && @.boolean() == true)' ought to work
(though in any other context you'd probably call it a code smell!) because
each operand is now a <JSON comparison predicate>.
Regards,
-Chap
On Thu, Jun 13, 2024 at 6:10 PM Chapman Flack <jcflack@acm.org> wrote:
On 06/13/24 16:43, David E. Wheeler wrote:
> Paging Mr. Eisentraut!
I'm not Mr. Eisentraut, but I have at last talked my way into some
access to the standard, so ...
Note 487 emphasizes that JSON path predicates "are not expressions;
instead they form a separate language that can only be invoked within
a <JSON filter expression>".
The only operators usable in a general expression (that is, a
<JSON path wff> are binary + - and binary * / % and unary + -
over a <JSON accessor expression>.
Inside a filter, you get to use a <JSON path predicate>. That's where
you can use ! and && and ||. But ! can only be applied to a
<JSON delimited predicate>: either a <JSON exists path predicate>,
or any other <JSON path predicate> wrapped in parentheses.
On 06/13/24 11:32, David E. Wheeler wrote:
> david=# select jsonb_path_query('true', '$ && $');
> david=# select jsonb_path_query('true', '$.boolean() && $.boolean()');
Those don't work because, as you recognized, they're not inside filters.
I'm content that the operators in the 'filter operators' table need to be within filter but then I cannot reconcile why this example worked:
david=# select jsonb_path_query('1', '$ >= 1');
jsonb_path_query
------------------
true
(1 row)
jsonb_path_query
------------------
true
(1 row)
David J.
On 06/13/24 21:24, David G. Johnston wrote:
> I'm content that the operators in the 'filter operators' table need to be
> within filter but then I cannot reconcile why this example worked:
>
> david=# select jsonb_path_query('1', '$ >= 1');
Good point. I can't either. No way I can see to parse that as
a <JSON path wff>.
Regards,
-Chap
On Thursday, June 13, 2024, Chapman Flack <jcflack@acm.org> wrote:
On 06/13/24 21:24, David G. Johnston wrote:
> I'm content that the operators in the 'filter operators' table need to be
> within filter but then I cannot reconcile why this example worked:
>
> david=# select jsonb_path_query('1', '$ >= 1');
Good point. I can't either. No way I can see to parse that as
a <JSON path wff>.
Whether we note it as non-standard or not is an open question then, but it does work and opens up a documentation question. It seems like it needs to appear in table T9.50. Whether it also should appear in T9.51 is the question. It seems like anything in T9.50 is allowed in a filter while the stuff in T9.51 should be limited to those things only allowed in a filter. Which suggests moving it from T9.51 to T9.50
David J.
On 06/13/24 21:46, David G. Johnston wrote:
>>> david=# select jsonb_path_query('1', '$ >= 1');
>>
>> Good point. I can't either. No way I can see to parse that as
>> a <JSON path wff>.
>
> Whether we note it as non-standard or not is an open question then, but it
> does work and opens up a documentation question.
Does the fact that it does work raise any potential concern that our
grammar is nonconformant in some way that could present a headache
somewhere else, or down the road with a later standard edition?
Regards,
-Chap
On Thursday, June 13, 2024, Chapman Flack <jcflack@acm.org> wrote:
On 06/13/24 21:46, David G. Johnston wrote:
>>> david=# select jsonb_path_query('1', '$ >= 1');
>>
>> Good point. I can't either. No way I can see to parse that as
>> a <JSON path wff>.
>
> Whether we note it as non-standard or not is an open question then, but it
> does work and opens up a documentation question.
Does the fact that it does work raise any potential concern that our
grammar is nonconformant in some way that could present a headache
somewhere else, or down the road with a later standard edition?
This isn’t new in v17 nor, to my knowledge, has the behavior changed, so I think we just need to live with whatever, likely minimal, chance of headache there is.
I don’t get why the outcome of a boolean producing operation isn’t just generally allowed to be produced, and would hope the standard would move toward allowing that across the board, and in doing so end up matching what we already have implemented.
David J.
On Jun 13, 2024, at 21:58, Chapman Flack <jcflack@acm.org> wrote:
>>>> david=# select jsonb_path_query('1', '$ >= 1');
>>>
>>> Good point. I can't either. No way I can see to parse that as
>>> a <JSON path wff>.
>>
>> Whether we note it as non-standard or not is an open question then, but it
>> does work and opens up a documentation question.
>
> Does the fact that it does work raise any potential concern that our
> grammar is nonconformant in some way that could present a headache
> somewhere else, or down the road with a later standard edition?
I believe this case is already covered in the docs as a Postgres-specific feature: predicate path expressions.
But even inside filters I don’t understand why &&, ||, at least, currently only work if their operands are predicate
expressions.Seems weird; and your notes above suggest that rule applies only to !, which makes slightly more sense.
D
On 06/13/24 22:16, David E. Wheeler wrote: > But even inside filters I don’t understand why &&, ||, at least, > currently only work if their operands are predicate expressions. > Seems weird; and your notes above suggest that rule applies only to !, > which makes slightly more sense. It's baked right into the standard grammar: || can only have a <JSON boolean conjunction> on its right and a <JSON boolean disjunction> on its left. && can only have a <JSON boolean negation> on its right and a <JSON boolean conjunction> on its left. The case for ! is even more limiting: it can't be applied to anything but a <JSON delimited predicate>. That can be either the exists predicate, or, any other <JSON path predicate> but wrapped in parentheses. The language seems sort of gappy in the same way XPath 1.0 was. XPath 2.0 became much more consistent and conceptually unified, only by that time, XML was old school, and JSON was cool, and apparently started inventing a path language. Regards, -Chap
On Jun 13, 2024, at 22:31, Chapman Flack <jcflack@acm.org> wrote: > It's baked right into the standard grammar: || can only have a > <JSON boolean conjunction> on its right and a <JSON boolean disjunction> > on its left. > > && can only have a <JSON boolean negation> on its right and a > <JSON boolean conjunction> on its left. Wow. > The case for ! is even more limiting: it can't be applied to anything > but a <JSON delimited predicate>. That can be either the exists predicate, > or, any other <JSON path predicate> but wrapped in parentheses. > > The language seems sort of gappy in the same way XPath 1.0 was. XPath 2.0 > became much more consistent and conceptually unified, only by that time, > XML was old school, and JSON was cool, and apparently started inventing > a path language. I suppose that’s the reason for this design. But if these sorts of limitations were changed in XPath, perhaps SQL-Next couldfix them, too. Thanks for citing the standard; super helpful. D