pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
7+ messages / 3 participants
[nested] [flat]
* BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
@ 2022-11-23 20:11 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2022-11-23 20:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: david@justatheory.com
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"';
```
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
@ 2022-11-24 17:16 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-11-24 17:16 UTC (permalink / raw)
To: david@justatheory.com; pgsql-bugs@lists.postgresql.org
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?
> ----------
> t
>
>
> 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:
>
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.
https://www.postgresql.org/message-id/CAKFQuwbeytffJkVnEqDyLZ=rQsznoTh1OgDoOF3VmOMkxcTMjA@mail.gmail...
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
@ 2022-11-24 17:26 David E. Wheeler <david@justatheory.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David E. Wheeler @ 2022-11-24 17:26 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
@ 2022-11-24 17:44 David G. Johnston <david.g.johnston@gmail.com>
parent: David E. Wheeler <david@justatheory.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-11-24 17:44 UTC (permalink / raw)
To: David E. Wheeler <david@justatheory.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
@ 2022-11-24 17:51 David E. Wheeler <david@justatheory.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David E. Wheeler @ 2022-11-24 17:51 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
@ 2022-11-24 17:55 David E. Wheeler <david@justatheory.com>
parent: David E. Wheeler <david@justatheory.com>
0 siblings, 1 reply; 7+ messages in thread
From: David E. Wheeler @ 2022-11-24 17:55 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored
@ 2022-11-27 22:47 David E. Wheeler <david@justatheory.com>
parent: David E. Wheeler <david@justatheory.com>
0 siblings, 0 replies; 7+ messages in thread
From: David E. Wheeler @ 2022-11-27 22:47 UTC (permalink / raw)
To: David E. Wheeler <david@justatheory.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-11-27 22:47 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-23 20:11 BUG #17694: In JSONPath expressions, characters between leading $ and dot appear to be ignored PG Bug reporting form <noreply@postgresql.org>
2022-11-24 17:16 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:26 ` David E. Wheeler <david@justatheory.com>
2022-11-24 17:44 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-24 17:51 ` David E. Wheeler <david@justatheory.com>
2022-11-24 17:55 ` David E. Wheeler <david@justatheory.com>
2022-11-27 22:47 ` David E. Wheeler <david@justatheory.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox