PostgreSQL has an extension to jsonpath:
** operator, which explores
arbitrary depth finding your values everywhere. At the same time, there is
lax mode, defined by the standard, providing a “relaxed” way for working
with json. In the
lax mode, accessors automatically unwrap arrays; missing
keys don’t trigger errors; etc. In short, it appears that the
lax mode aren’t designed to be together :)
The story started with the bug report.
The simplified version is below. Jsonpath query is intended to select the
value of key
"y" everywhere. But it appears to select these values twice.
1 2 3 4 5 6 7 8 9
This case looks like a bug. But is it? Let’s dig into details. Let’s split
the jsonpath query into two parts: one containing the
** operator and another
having the key accessor.
1 2 3 4 5 6 7 8 9 10 11 12 13
As you can see, the
** operator selects every child in the json document as
expected. The key accessor extracts corresponding values from both objects
themselves and their wrapping arrays. And that’s also expected in the
mode. So, it appears there is no bug; everything works as designed, although
it’s surprising for users.
Finally, I’ve committed a paragraph to the
which explicitly clarifies this issue.
It seems that
lax mode and
** operator just aren’t designed to be
used together. If you need
** operator, you can use
strict mode. and
everything is intuitively correct.
1 2 3 4 5 6 7