BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
The following bug has been logged on the website:
Bug reference: 17694
Logged by: David Wheeler
Email address: david@justatheory.com
PostgreSQL version: 15.1
Operating system: macOS
Description:
The correct way to specify an absolute JSON path expression is to start with
`$.`, as in:
```
david=# select '{"foo": 1}' @? '$.foo';
?column?
----------
t
```
If, however, you omit the dot (`.`), the expression incorrectly always
evaluates to true!
```
david=# select '{"foo": 1}' @? '$foo';
?column?
----------
t
david=# select '{"foo": 1}' @? '$"foo bar"';
?column?
----------
t
david=# select '{"foo": 1}' @? '$"foo bar".bar';
?column?
----------
f
```
It looks like the text between the `$` and `.` is ignored. I don't think
this is right. Shouldn't it be a syntax error? Seems to properly complain if
using the same pattern in subpaths:
```
david=# select '{"foo": 1}' @? '$.foo"foo bar"';
ERROR: syntax error at or near """ of jsonpath input
LINE 1: select '{"foo": 1}' @? '$.foo"foo bar"';
```
On Thu, Nov 24, 2022 at 3:28 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17694
Logged by: David Wheeler
Email address: david@justatheory.com
PostgreSQL version: 15.1
Operating system: macOS
Description:The correct way to specify an absolute JSON path expression is to start
with
`$.`, as in:```
david=# select '{"foo": 1}' @? '$.foo';
?column?
----------
t
```If, however, you omit the dot (`.`), the expression incorrectly always
evaluates to true!```
david=# select '{"foo": 1}' @? '$foo';
?column?
----------
tIt looks like the text between the `$` and `.` is ignored. I don't think
this is right. Shouldn't it be a syntax error? Seems to properly complain
if
using the same pattern in subpaths:
There is a bug in this area though the syntax itself is valid since you've
simply defined a variable.
While this was in moderation I posted a more detailed report and my
research on the issue.
/messages/by-id/CAKFQuwbeytffJkVnEqDyLZ=rQsznoTh1OgDoOF3VmOMkxcTMjA@mail.gmail.com
David J.
On Nov 24, 2022, at 12:16, David G. Johnston <david.g.johnston@gmail.com> wrote:
There is a bug in this area though the syntax itself is valid since you've simply defined a variable.
Sorry, I don’t follow. What variable is defined? I mean `$` is a variable for the whole expression, and always required at the start of a JSONPath, AFAICT. But when running
select '{"foo": 1}' @? ‘$foo'
or
select '{}' @? ‘$foo'
(both of which incorrectly return true), there is no `$foo` path; it has to be `$.foo`.
Best,
David
On Thu, Nov 24, 2022 at 10:27 AM David E. Wheeler <david@justatheory.com>
wrote:
On Nov 24, 2022, at 12:16, David G. Johnston <david.g.johnston@gmail.com>
wrote:There is a bug in this area though the syntax itself is valid since
you've simply defined a variable.
Sorry, I don’t follow. What variable is defined? I mean `$` is a variable
for the whole expression, and always required at the start of a JSONPath
JSONPath can do/represent much more than you are thinking here.
https://www.postgresql.org/docs/current/datatype-json.html#DATATYPE-JSONPATH
Note the second entry ($varname) in table 8.24
David J.
On Nov 24, 2022, at 12:44, David G. Johnston <david.g.johnston@gmail.com> wrote:
JSONPath can do/represent much more than you are thinking here.
https://www.postgresql.org/docs/current/datatype-json.html#DATATYPE-JSONPATH
Note the second entry ($varname) in table 8.24
Ah, I see. SQL/JSON syntax gets a bit confusing relative to other flavors.
D
On Nov 24, 2022, at 12:51, David E. Wheeler <david@justatheory.com> wrote:
Ah, I see. SQL/JSON syntax gets a bit confusing relative to other flavors.
Yeah, this doesn’t seem right, either:
david=# select jsonb_path_exists('{"foo": {"bar": true}}', '$path', '{"path": "$.foo.bax"}');
jsonb_path_exists
-------------------
t
D
On Nov 24, 2022, at 12:55, David E. Wheeler <david@justatheory.com> wrote:
Yeah, this doesn’t seem right, either:
david=# select jsonb_path_exists('{"foo": {"bar": true}}', '$path', '{"path": "$.foo.bax"}');
jsonb_path_exists
—————————
t
david=# select '{}' @? '$.emails.work == "hi@home.com"';
?column?
----------
t
Works properly with `@@`, though:
david=# select '{}' @@ '$.emails.work == "hi@home.com"';
?column?
----------
f
Have to admit I find this pretty weird. I see that the docs say that this Postgres-specific boolean predicate syntax is required for @@, but it doesn’t say it doesn’t apply to @? But the `?()` filter syntax works as expected with @?:
david=# select '{}' @? '$.emails.work ?(@ == "hi@home.com")';
?column?
----------
f
Wondering if I should avoid `@?`. Frankly I don’t understand the difference between `@@` and `@?`.
Best,
David