MIME-Version: 1.0
From: Alex R <ralienpp@gmail.com>
Date: Thu, 16 Dec 2021 16:56:48 +0100
Message-ID: 
 <CANK1NR1E1CyHcqF+xQvBYBY0+G4GGL_HKDDSad2am74zz_N3mg@mail.gmail.com>
Subject: JSONpath query that returns paths to the matches
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000002ab8a05d3457911"
Archived-At: 
 <https://www.postgresql.org/message-id/CANK1NR1E1CyHcqF%2BxQvBYBY0%2BG4GGL_HKDDSad2am74zz_N3mg%40mail.gmail.com>
Precedence: bulk

--00000000000002ab8a05d3457911
Content-Type: text/plain; charset="UTF-8"

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"');

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

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

<div dir=3D"ltr">Hi,<div><br></div><div>I am trying to figure out whether P=
ostgres 14 allows me to find the paths to certain values in the JSON, rathe=
r than the values themselves. To illustrate, here is a test query:</div><di=
v><br></div><div>SELECT JSONB_PATH_QUERY($${<br>=C2=A0 &quot;this&quot;: &q=
uot;that&quot;,<br>=C2=A0 &quot;that&quot;: [{&quot;x&quot;: &quot;aaa&quot=
;},{&quot;y&quot;: &quot;missed&quot;}],<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;strict $.**.&quot;x&quot;&#39;);<br></div><div><br></div><div=
>It returns 2 matches: &quot;aaa&quot; and &quot;bbb&quot;. However, what I=
&#39;d like to get instead is 2 paths that point to the matches, i.e.,:</di=
v><div>- $.that[0]</div><div>- $.nested.deep</div><div><br></div><div>Can t=
his be accomplished using means that are available out of the box? If not, =
what would be a sane way of implementing it?</div><div><br></div><div><br><=
/div><div>Thank you for your help,</div><div>Alex</div></div>

--00000000000002ab8a05d3457911--