MIME-Version: 1.0
References: 
 <CANK1NR1E1CyHcqF+xQvBYBY0+G4GGL_HKDDSad2am74zz_N3mg@mail.gmail.com>
 <CAKFQuwYVhCVwa0qfTfpvkziLnj_ogdpGruJhFqJ=Zig=n2+LuA@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwYVhCVwa0qfTfpvkziLnj_ogdpGruJhFqJ=Zig=n2+LuA@mail.gmail.com>
From: Alex R <ralienpp@gmail.com>
Date: Mon, 20 Dec 2021 01:19:43 +0100
Message-ID: 
 <CANK1NR2=WvSBRAL=HKsZUHyoK_uTZKajwoE-bC=Q2GNHzSkv0g@mail.gmail.com>
Subject: Re: JSONpath query that returns paths to the matches
To: pgsql-novice <pgsql-novice@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000010b9d905d388d90c"
Archived-At: 
 <https://www.postgresql.org/message-id/CANK1NR2%3DWvSBRAL%3DHKsZUHyoK_uTZKajwoE-bC%3DQ2GNHzSkv0g%40mail.gmail.com>
Precedence: bulk

--00000000000010b9d905d388d90c
Content-Type: text/plain; charset="UTF-8"

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
```

Porting this to pl/python is left as an exercise for the reader :-)

--00000000000010b9d905d388d90c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi David,</div><div><br></div><div>Thank you for the =
swift reply. After ruling out the option of doing that by leveraging JSONPa=
th, I took a different approach and wrote a function in=C2=A0<span style=3D=
"font-family:arial,helvetica,sans-serif">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.</span></div><div><span styl=
e=3D"font-family:arial,helvetica,sans-serif"><br></span></div><div><span st=
yle=3D"font-family:arial,helvetica,sans-serif"><br></span></div><div><span =
style=3D"font-family:arial,helvetica,sans-serif">For others who might be co=
nfronted with a similar problem in the future, here is a minimal example th=
at uses Python and a library called=C2=A0</span>jsonpath_ng, which tells yo=
u what the match is and where it occurs<span style=3D"font-family:arial,hel=
vetica,sans-serif">:</span></div><div><span style=3D"font-family:arial,helv=
etica,sans-serif"><br></span></div><div><span style=3D"font-family:arial,he=
lvetica,sans-serif">```</span></div><div># pip install jsonpath_ng<br><br>i=
mport json<br>from jsonpath_ng import jsonpath, parse<br><br>raw =3D &#39;&=
#39;&#39;{<br>=C2=A0 &quot;this&quot;: &quot;that&quot;,<br>=C2=A0 &quot;th=
at&quot;: [{&quot;x&quot;: &quot;aaa&quot;},{&quot;y&quot;: &quot;missed&qu=
ot;}],<br>=C2=A0 &quot;nested&quot;: {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 &quot=
;deep&quot;: {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 &quot;x&quot;: &quot;bbb&quot=
;<br>=C2=A0 =C2=A0 =C2=A0 }<br>=C2=A0 }<br>}&#39;&#39;&#39;<br><br>data =3D=
 json.loads(raw)<br>query =3D parse(&#39;$..x&#39;)<br><br>results =3D quer=
y.find(data)<br>for entry in results:<br>=C2=A0 =C2=A0 print(f&#39;{entry.f=
ull_path} -&gt; {entry.value}&#39;)<br><br># that.[0].x -&gt; aaa<br># nest=
ed.deep.x -&gt; bbb<br></div><div><span style=3D"font-family:arial,helvetic=
a,sans-serif">```</span></div><div><span style=3D"font-family:arial,helveti=
ca,sans-serif"><br></span></div><div><font face=3D"arial, helvetica, sans-s=
erif">Porting this to pl/python is left as an exercise for the reader :-)</=
font></div></div>

--00000000000010b9d905d388d90c--