How do I use spring data jpa to query jsonb column?

Question

I'm having a problem getting this native query right against a postgres 9.4 instance.

My repository has a method:

 @Query(value = "SELECT t.* " +
            "FROM my_table t " +
            "WHERE t.field_1 = ?1 " +
            "AND t.field_2 = 1 " +
            "AND t.field_3 IN ?2 " +
            "AND t.jsonb_field #>> '{key,subkey}' = ?3",
            nativeQuery = true)
    List<Entity> getEntities(String field1Value,
                                   Collection<Integer> field3Values,
                                   String jsonbFieldValue);

But the logs show this:

SELECT t.* FROM my_table t 
WHERE t.field_1 = ?1 
  AND t.field_2 = 1 
  AND t.field_3 IN ?2 
  AND t.jsonb_field ? '{key,subkey}' = ?3

And I get this exception:

Internal Exception: org.postgresql.util.PSQLException: No value specified for parameter 2.

I logged the parameters directly before method invocation, and they are all supplied.

I'm not sure why #>> shows ? in the log. Do I need to escape #>>? Do I need to format the collection for IN? Do I need to escape the json path?

When I execute the query directly against the db, it works. Example:

SELECT *
FROM my_table t
WHERE t.field_1 = 'xxxx'
  AND t.field_2 = 1
  AND t.field_3 IN (13)
  AND t.jsonb_field #>> '{key,subkey}' = 'value'

Answer 1

I found very helpful the Specification api from spring data.
Let's say we have an entity with name Product and a property with name title of type JSON(B).
I assume that this property contains the title of the Product in different languages. An example could be: {"EN":"Multicolor LED light", "EL":"Πολύχρωμο LED φώς"}.
The source code below finds a (or more in case it is not a unique field) product by title and locale passed as arguments.

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
}


public class ProductSpecification implements Specification<Product> {

    private String locale;
    private String titleToSearch;

    public ProductSpecification(String locale, String titleToSearch) {
        this.locale = locale;
        this.titleToSearch = titleToSearch;
    }

    @Override
    public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(this.locale)), this.titleToSearch);
    }
}


@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public List<Product> findByTitle(String locale, String titleToSearch) {
        ProductSpecification cs = new ProductSpecification(locale, titleToSearch);
        return productRepository.find(cs);
        // Or using lambda expression - without the need of ProductSpecification class.
//      return productRepository.find((Root<ProductCategory> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
//          return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(locale)), titleToSearch);
//      });
    }
}

You can find another answer about the way you should use the Spring Data here.
Hope that helps.

Answer 2

Maybe this is an old topic, but I'm putting here search in jsonb by field using spring specification.

If you want to search with "LIKE" you need to create like disjunction with the following code:

final Predicate likeSearch = cb.disjunction();

After that, let's assume u have jsonb field in your object which is address, and address has 5 fields. To search in all these fields you need to add "LIKE" expression for all fields:

for (String field : ADDRESS_SEARCH_FIELDS) {
                likeSearch.getExpressions().add(cb.like(cb.lower(cb.function("json_extract_path_text", String.class,
                        root.get("address"), cb.literal(field))), %searchKey%));
            }

Where cb is the same criteriaBuilder. %searchKey% is what you want to search in address fields.

Hope this helps.

Answer 3

If the operator is being converted to a question mark for one reason or another, then you should try using the function instead. You can find the corresponding function using \doS+ #>> in the psql console. It tells us the function called is jsonb_extract_path_text. This would make your query:

@Query(value = "SELECT t.* " +
        "FROM my_table t " +
        "WHERE t.field_1 = ?1 " +
        "AND t.field_2 = 1 " +
        "AND t.field_3 IN ?2 " +
        "AND jsonb_extract_path_text(t.jsonb_field, '{key,subkey}') = ?3",
        nativeQuery = true)

Answer 4

I suggest not following this way, I prefer to follow generic CRUD way (also working on advanced auto generated DAO methods in way of StrongLoop Loopback does, for Spring Data Rest maven plugin, but it is experimental in the moment only). But with this JSON, now what to do... I am looking for something similar to MongoDB JSON processing in Spring Data via @Document annotation, however this is not yet available. But there are other ways :-)

In general it is about implementing your JSON user type (UserType interface):

public class YourJSONBType implements UserType {

Finally you need to enhance your JPA classes with specification of your implemented user type:

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@TypeDef(name = "JsonbType", typeClass = YourJSONBType.class)
public class Person {
    @Id
    @GeneratedValue
    private Long id;

    @Column(columnDefinition = "jsonb")
    @Type(type = "JsonbType")
    private Map<String,Object> info;
}

look at another related articles here: Mapping PostgreSQL JSON column to Hibernate value type

The full implementation example is available here:

• https://github.com/nzhong/spring-data-jpa-postgresql-json
• https://github.com/mariusneo/postgres-json-jpa

Similar, but little different example is available here: http://www.wisely.top/2017/06/27/spring-data-jpa-postgresql-jsonb/?d=1

Answer 5

You can also use the FUNC JPQL keywork for calling custom functions and not use a native query.
Something like this,

@Query(value = "SELECT t FROM my_table t "
        + "WHERE t.field_1=:field_1 AND t.field_2=1 AND t.field_3 IN :field_3 "
        + "AND FUNC('jsonb_extract_path_text', 'key', 'subkey')=:value")
List<Entity> getEntities(@Param("field_1") String field_1, @Param("field_3") Collection<Integer> field_3, @Param("value") String value);