Sample Header Ad - 728x90

How to obtain the path to the match of a JSONPath query in PostgreSQL 14?

5 votes
1 answer
2053 views
Here is a minimal working example that illustrates my point, the query finds all the values of keys named "$" at any level in the given JSON:
select jsonb_path_query(
$$
{
  "id": "test",
  "data": [
    {
      "$": "ref1"
    },
    {
      "$": "ref2"
    },
    {
      "engine": "dual",
      "fuel": "nuclear"
    }
  ],
  "owner": {
    "$": "ref3"
  },
  "bazaar": {
        "manager": {
        "$": "ref4"
      }
  }
}$$,
    'strict $.**."$"'
);
The returned results are:
"ref1"
"ref2"
"ref3"
"ref4"
However, what I need is a list of paths to these matches, i.e.,
"$['data']['$']",
"$['data']['$']",
"$['owner']['$']",
"$['bazaar']['manager']['$']"
How can this be accomplished? Context: a database contains jsonb values, where the $ sign is a placeholder for a value stored in a different table. The choice of $ is unfortunate, because it is also a part of JSONPath syntax, but this is a legacy system, so things have to stay this way. Let's focus on "ref4" from my sample query. In another table, ref4 points to {"name": "Balthazar", "occupation": "engineer"}. My goal is to substitute the references in the original structure with the objects they point to. In other words, the JSON will change from ... "bazaar": {"owner": {"$": "ref4"}} to "bazaar": {"owner": {"name": "Balthazar", "occupation": "engineer"}}. My intention is to write a function that will be called recursively, which looks for the $ entries and replaces them with the values from the other table, then returns the final JSON. In the same context, I'd like to find out if you can suggest better ways to accomplish the objective.
Asked by ralien (153 rep)
Dec 15, 2021, 02:47 PM
Last activity: Dec 11, 2022, 02:46 PM