Find by key in any location of JSONB field-CodePudding

I have a Postgres table with the JSOB field.

Content example of body field

{
 "name": "example name",
 "text": "example text",
 "story":
  {
    "id":"UUID1"
  },
 "reference":
  {
   "type":"URI",
   "content":
    {
     "id":"UUID2"
    }
  }
}

I need to find documents by ID, but they can be in any place.

The only thing I found till now is

 @Repository
 public interface Repository extends JpaRepository<EntityWithJsonbField, String>{
 ...
   Query(value="SELECT * FROM table_with_jsonb_field WHERE body ->> 'id' = :id ", nativeQuery = true)
   List<EntityWithJsonbField> findAllWithId(@Param("id")String id);
...
}

but it is searching only by id in the root of the document.

Also tried '$..id' (JSON PATH) instead of 'id' with no luck.

Is it even possible?

PS later, I will need to update found content. So, any links to relevant documentation are welcome. :)

Update: Thanks to a_horse_with_no_name it is working now!

 @Repository
 public interface Repository extends JpaRepository<EntityWithJsonbField, String>{
 ...
   Query(value="SELECT * FROM content_type WHERE body @@ cast(concat('$.**.id == \"',:id,'\"') as jsonpath)", nativeQuery = true)
   List<EntityWithJsonbField> findAllWithId(@Param("id")String id);
...
}

CodePudding user response：

With plain SQL this can be done like this:

select *
from the_table
where jsonb_field @@ '$.**.id == "UUID2"';

The expression $.**.id iterates recursively through all object keys and matches the key id with the value UUID2.

However the comparison value for that expression can not be passed as a parameter directly. So you need to concatenate create a string in SQL by passing a (JPA) parameter and then cast that string to a jsonpath

"select from the_table  "  
"where jsonb_field @@ concat('$.**.id == \"',:id,'\"')::jsonpath"

If your obfuscation layer chokes on the :: you can use:

"where jsonb_field @@ cast(concat('$.**.id == \"',:id,'\"') as jsonpath)"

I have only tested this with plain JDBC, not with JPA, but as the :id is not contained inside a SQL string (only inside the Java String), this should work with JPA.

Another option is to use jsonb_path_exists() instead, which is easier to use if parameters should be passed into the JSON path expression:

"select from the_table  "  
"where jsonb_path_exists(jsonb_field, '$.**.id ? (@ == $id)', jsonb_build_object('id', :id))"

However, jsonb_path_exists() can't use an index on the column jsonb_field