CODEWITHSUNDEEP
spring-bootjpaspring-data-jpanativequery
Sam
Sam

Reputation: 99

Query in jsonb (JSON array) column with JPA native query

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": "[email protected]"
},
{
    "user": "test2",
    "email": "[email protected]"
}]

In postgres client, Below query is working absolutely fine.

SELECT * FROM table1 WHERE "user_details" @> '[{"email": "[email protected]"}]';

The same in @Repository I want to achieve through native query where email value([email protected]) 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\" : \"[email protected]\"}]'")

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\" : \"[email protected]\"}]'")
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.

Upvotes: 2

Views: 8272

Answers (2)

Rajesh K
Rajesh K

Reputation: 1

{ "quoteId":2024, "quoteName":"EDE", "quoteDetails" :[ "id":3156, "metadata":[ "code":"vnf", "set": [ { "key":"abc" }, { "key":"1955" } ] ] ]

}

{ "quoteId":2025, "quoteName":"EDE2", "quoteDetails" :[ "id":3157, "metadata":[ "code":"vnf2", "set": [ { "key":"xyz" }, { "key":"2050" } ] ] ]

}

What will be query for this .
JsonObject obj= new JsonObject();
obj.put("key", key);
JsonArray keyNode = new JsonArray();
keyNode.put(0, obj);
JsonObject setNode= new JsonObject();
setNode.put("set", keyNode);
JsonArray searchNode=new Jsonorary();
searchNode.put(0, setNode);
Query(value="SELECT * FROM quote WHERE metadata@> cast(:key as jsonb)",nativeQuery=true) List

Upvotes: 0

Rahul Bhagat
Rahul Bhagat

Reputation: 56

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

Upvotes: 4

Related Questions