JPA and JSON operator native query

Question

I'm trying to make this query work in JPA:

SELECT * FROM contrat WHERE contrat_json @> '{"nom" :"hever"}';

It works perfectly with postgresql but when I integrate it with JPA, I get the following error:

Parameter with that position [1] did not exist

My code:

 @Transactional
 @Query(nativeQuery = true,value = "select p from Contrat p where contrat_json @> '{\"nom\":\":nom\"}'")
    public List<Contrat> findByNomRestrict(@Param("nom") String nom);

I think it does not recognize @> despite native query, do you have an idea?

If you have a `@Param` parameter declared in your spring repository method, you should mention it in the query itself too, like `contrat_json @> jsonb_build_object('nom', :nom)` -- `:nom` within the string literal will still mean the characters: `:nom`, not the bound parameter. — pozs, Mar 20 '17 at 12:38
i use spring starter jpa 1.5.1. @pozs i use your method but i had this error : "function jsonb_build_object(unknown, character varying) does not exist Indice : No function matches the given name and argument types. You might need to add explicit type casts." — Guillaume Hochart, Mar 20 '17 at 13:50
@GuillaumeHochart that function exists on PostgreSQL 9.5+ -- For 9.4, you could use `CAST(json_build_object(...) AS jsonb)` -- For 9.3-, there is no `@>` operator anyway :) — pozs, Mar 20 '17 at 13:57
@coladict how is that even remotely related to 9.3- does not have any (for json)? — pozs, Mar 20 '17 at 14:00
Well OP hasn't mentioned a version. Why do you assume it's 9.3? — coladict, Mar 20 '17 at 14:02
@coladict i didn't assume any version: that's why a listed every option from 9.3- to 9.5+ — pozs, Mar 20 '17 at 14:06
i use the last version of maven repository : 9.4.1207. I try your propositon but j always had this error "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet". I can't use 9.6, there is no maven repository. for this moment my code @Transactional @Query(nativeQuery = true,value = "select p from Contrat p where contrat_json @> CAST(jsonb_build_object('nom', :nom)) AS jsonb") public List findByNomRestrict(@Param("nom") String nom); — Guillaume Hochart, Mar 20 '17 at 14:18
@GuillaumeHochart you have an extra closing bracket `)` (which is not needed) inside the `CAST` call (which is not closed with `)` properly). Also, please post the most inner exception next time as the `could not extract ResultSet` is not really useful. — pozs, Mar 20 '17 at 14:23
oups sorry, I misplace the parentheses. Otherwise I get the same error as just now : "ERROR: function jsonb_build_object(unknown, character varying) does not exist Indice : No function matches the given name and argument types. You might need to add explicit type casts." — Guillaume Hochart, Mar 20 '17 at 14:31
@GuillaumeHochart you are still using the wrong function (the one for PostgreSQL 9.5+), see my answer, or: http://rextester.com/AUHP11519 — pozs, Mar 20 '17 at 14:36
not I use it as you : @Transactional @Query(nativeQuery = true,value = "select p from Contrat p where contrat_json @> CAST(jsonb_build_object('nom', :nom) AS jsonb)") public List findByNomRestrict(@Param("nom") String nom); — Guillaume Hochart, Mar 20 '17 at 14:44
@GuillaumeHochart no, the function is `json_build_object` (**without the B**) for 9.4. Read my answer. Please. — pozs, Mar 20 '17 at 14:48
Sorry i confuse, i havn't seen the difference. thank for the helping. Now i have new error. I'll look a bit before posing it here :) — Guillaume Hochart, Mar 20 '17 at 15:03

pozs · Answer 1 · 2017-03-20T14:37:13.500

Parameter holders are not understood inside literals: '...:nom...' will contain the characters :nom, not the bound values of nom.

For PostgreSQL 9.5 (and later), use:

SELECT * FROM contrat WHERE contrat_json @> jsonb_build_object('nom', :nom)

For 9.4:

SELECT * FROM contrat WHERE contrat_json @> CAST(json_build_object('nom', :nom) AS jsonb)

For 9.3 (and earlier), there is no JSON containment operator (neither the jsonb type).

http://rextester.com/AUHP11519

score 3 · Answer 2 · answered Jun 27 '17 at 19:21

I had similar problem with my native query. The jsonb field name is called data, and it's simple

{ 
   "name" : "genderList", 
   "displayName" : "gender list" 
}

I want to find by name with JpaRepository, and here is my Repository

@Repository
public interface LookupListRepository extends JpaRepository<LookupList, UUID>
{
    @Query(value = "SELECT * FROM lookup_list WHERE data->>'name' = :name", 
            nativeQuery = true)
    List<LookupList> findByName(@Param("name") String name);
}

You need nativeQuery = true. With nativeQuery = true, this works as well.

SELECT * FROM lookup_list WHERE jsonb_extract_path_text(data, 'name') = :name

I see your @Transactional annotation, I assume you have the native query on top of application service method. Can you try moving all native query's in repository and use JpaRepository, and use the repository method in your application service? Here is how my application service uses the repository.

public class LookupListServiceImpl implements LookupListService
{
    @Autowired
    LookupListRepository lookupListRepository;

    @Override
    @Transactional
    public void changeLookupList(LookupListDto lookupListDto)
    {
        List<LookupList> lookupLists = lookupListRepository.findByName(lookupListDto.getName());
        ...
    }

}

Reference for JPA repository http://docs.spring.io/spring-data/jpa/docs/1.3.0.RELEASE/reference/html/jpa.repositories.html

score 2 · Answer 3 · answered Mar 20 '17 at 13:58

2

With PostgreSQL and JSON you'll probably run into needing ? or other strange operators, so it's better you just use their function equivalents, instead. You can look them up in the psql console like this \doS+ @>.

Your query is not native, as the parameter says.

select p from Contrat p where...

Will only give you an error when it reaches the database.

Try something like

@Query(nativeQuery = true, value = "select * from Contrat where jsonb_contains(contrat_json, :nom )")

and just bind "{\"nom\":\"" + param + "\"}" as the parameter

answered Mar 20 '17 at 13:58

coladict

4,799
1
16
27

1

Manipulating JSON with simple concatenation is very risky. Also, the function counterpart of `@>` (which is the `jsonb_contains`) cannot use any index on `contrat_json`. -- And the `?` operator can be escaped anyway. – pozs Mar 20 '17 at 14:08
Well, the other way is you use a JSON builder of some sort, but this is a simplified case and `@>` is what OP asked for. As for escaping the `?`, I've tried in Hibernate. You add one level of escaping and the JDBC driver complains about unset parameters, you add two levels of escaping and Hibernate complains about unset parameters. No way to satisfy both. – coladict Mar 20 '17 at 14:19

JPA and JSON operator native query

3 Answers3

Linked