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?

Answer 1

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