Обсуждение: JSONpath query that returns paths to the matches
Hi,
I am trying to figure out whether Postgres 14 allows me to find the paths to certain values in the JSON, rather than the values themselves. To illustrate, here is a test query:
SELECT JSONB_PATH_QUERY($${
"this": "that",
"that": [{"x": "aaa"},{"y": "missed"}],
"nested": {
"deep": {
"x": "bbb"
}
}
}$$, 'strict $.**."x"');
"this": "that",
"that": [{"x": "aaa"},{"y": "missed"}],
"nested": {
"deep": {
"x": "bbb"
}
}
}$$, 'strict $.**."x"');
It returns 2 matches: "aaa" and "bbb". However, what I'd like to get instead is 2 paths that point to the matches, i.e.,:
- $.that[0]
- $.nested.deep
Can this be accomplished using means that are available out of the box? If not, what would be a sane way of implementing it?
Thank you for your help,
Alex
On Thu, Dec 16, 2021 at 8:57 AM Alex R <ralienpp@gmail.com> wrote:
It returns 2 matches: "aaa" and "bbb". However, what I'd like to get instead is 2 paths that point to the matches, i.e.,:- $.that[0]- $.nested.deepCan this be accomplished using means that are available out of the box? If not, what would be a sane way of implementing it?
1. Not that I can see documented.
2a See if some other language that has a PL extension for PostgreSQL can do this more easily and write a function in the language.
2b Create a custom C language function based off of one of the existing "path exists" functions that keeps track of its position in the graph and the returns that instead of a boolean/whatever.
2c Create your own procedural code, which can be done in pl/pgsql, that performs the graph traversal and captures the data like in 2b.
David J.
Hi David,
Thank you for the swift reply. After ruling out the option of doing that by leveraging JSONPath, I took a different approach and wrote a function in pl/pgsql that does not need these paths at all. I could deviate a bit, because I had some wiggle room in the context of the bigger problem I was working on.
For others who might be confronted with a similar problem in the future, here is a minimal example that uses Python and a library called jsonpath_ng, which tells you what the match is and where it occurs:
```
# pip install jsonpath_ng
import json
from jsonpath_ng import jsonpath, parse
raw = '''{
"this": "that",
"that": [{"x": "aaa"},{"y": "missed"}],
"nested": {
"deep": {
"x": "bbb"
}
}
}'''
data = json.loads(raw)
query = parse('$..x')
results = query.find(data)
for entry in results:
print(f'{entry.full_path} -> {entry.value}')
# that.[0].x -> aaa
# nested.deep.x -> bbb
import json
from jsonpath_ng import jsonpath, parse
raw = '''{
"this": "that",
"that": [{"x": "aaa"},{"y": "missed"}],
"nested": {
"deep": {
"x": "bbb"
}
}
}'''
data = json.loads(raw)
query = parse('$..x')
results = query.find(data)
for entry in results:
print(f'{entry.full_path} -> {entry.value}')
# that.[0].x -> aaa
# nested.deep.x -> bbb
```
Porting this to pl/python is left as an exercise for the reader :-)