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?

Solution 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

Solution 2

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

Solution 3

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

Comments

[pozs]

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.

[coladict]

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.