1

I am using JPA where one of the entity holds user_details (jsonb) column.

Below is user_details json array data on which I want to query.

[{
    "user": "test1",
    "email": "test1@gmail.com"
},
{
    "user": "test2",
    "email": "test2@gmail.com"
}]

In postgres client, Below query is working absolutely fine.

SELECT * FROM table1 WHERE "user_details" @> '[{"email": "test1@gmail.com"}]';

The same in @Repository I want to achieve through native query where email value(test1@gmail.com) would be dynamic. Here is my code :

1. @Query(value ="select * from table1 WHERE user_details @> '[{\"email\": :email}]'", nativeQuery = true)
List<Entity> findByEmail(@Param("email") String email);

ERROR: invalid input syntax for type json

Detail: Expected JSON value, but found ":".

Where: JSON data, line 1: [{"email": :...

2. @Query(value ="select * from table1 WHERE user_details @> '[{\"email\": ?1}]'", nativeQuery = true)
List<Entity> findByEmail(String email);

ERROR: invalid input syntax for type json

Detail: Token "?" is invalid.

Where: JSON data, line 1: [{"email": ?...

3. @Query(value ="select * from table1 WHERE user_details @> :param", nativeQuery = true)
List<Entity> findByEmail(@Param("param") String param); (i.e. param= "'[{ \"email\" : \"test1@gmail.com\"}]'")

ERROR: operator does not exist: jsonb @> character varying

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Edited:

4. @Query(nativeQuery = true, value = "select * from table1 where jsonb_contains(user_details , :param )") (i.e. param= "'[{ \"email\" : \"test1@gmail.com\"}]'")
List<Entity> findByEmail(@Param("param") String email);

ERROR: function jsonb_contains(jsonb, character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

5. @Query(nativeQuery = true, value = "select * from table1 where user_details @> jsonb_build_object('email', :param )")
List<Entity> findByEmail(@Param("param") String email);

NO ERROR but not giving the result.

6. @Query(nativeQuery = true, value = "select * from table1 where user_details ->>'email' = :param")
List<Entity> findByEmail(@Param("param") String email);

NO ERROR but not giving the result.

7. @Query(nativeQuery = true, value = "select * from table1 WHERE jsonb_extract_path_text(user_details , 'email') = :param")
List<Entity> findByEmail(@Param("param") String email);

NO ERROR but not giving the result.

These native queries are not giving me the expected result.

Please help me with the native query where I can bind the parameter with query. Thanks in advance.

Sam
  • 79
  • 1
  • 12
  • can this https://stackoverflow.com/a/42906278/12854146 help you? – tremendous7 Jun 05 '21 at 17:02
  • @tremendous7 Thanks for the reference but provided solutions does not work. I have updated the description (example: 4, 5, 6, 7) where I have tried discussed queries. None of them are working. – Sam Jun 05 '21 at 17:58
  • can you confirm there is no typo in the param/email parameter? could you report the log of some of the queries for which you had no results and no errors? – tremendous7 Jun 05 '21 at 18:22
  • @tremendous7 No I have cross verified there is no typo in the param/email parameter. Find the below logs where no result found without error. 5. Hibernate: select * from table1 where user_details @> jsonb_build_object('email', ? ) 6. Hibernate: SELECT * FROM table1 WHERE user_details ->>'email' = ? 7. Thread starvation or clock leap detected (housekeeper delta=25m34s579ms804µs400ns). Hibernate: select * from table1 WHERE jsonb_extract_path_text(user_details, 'email') = ? – Sam Jun 05 '21 at 20:16

1 Answers1

4

Before you call this function, create a jsonarray:

JSONObject obj = new JSONObject();
obj.put("email", email);
JSONArray a = new JSONArray();
a.add(0, obj);

Call the function by passing jsonArray string

findByEmail(a.toJSONString());

Your query should be :

@Query(nativeQuery = true, value = "select * from table1 where user_details @> cast(:param as jsonb)")
List<Entity> findByEmail(@Param("param") String email);
Rahul Bhagat
  • 56
  • 2
  • If param parameter is null, then another problem comes up :) since we use native query, "...where :param is null OR user_details @> cast(:param as jsonb)" doesn't work properly! To handle this problem, you can use the solution under this link: https://stackoverflow.com/a/67848149/1194553 – séan35 Mar 17 '23 at 19:17