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

Create a table in postgres DB

CREATE TABLE shared.my_data (
id serial PRIMARY KEY,
my_config jsonb
);

Insert data into the table

INSERT into shared.my_data (id, my_config) VALUES( 1,
'{"useTime": true,
"manualUnassign": false,
"require":true,
"blockTime":10,
"additionalHours":1,
"availablegroups":[10,20,30]
}')

Check data in table:

select * from shared.tenant_data 

Spring boot Java project Java version: 11 Spring version: 2.7.1

Maven dependency on POM.xml file. For postgres JOSNB, we need particular

vladmihalcea dependency version 2.14.0

    <dependency>
        <groupId>com.vladmihalcea</groupId>
        <artifactId>hibernate-types-52</artifactId>
        <version>2.14.0</version>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        <version>2.7.1</version>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
        <version>2.7.1</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.7.1</version>
    </dependency>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>

JSON object class

import com.fasterxml.jackson.annotation.JsonProperty;

import java.util.List;

public class MyConfig {
@JsonProperty("useTime")
private boolean useTime;
@JsonProperty("manualUnassign")
private boolean manualUnassign;
@JsonProperty("require")
private boolean require;
@JsonProperty("additionalHours")
private int additionalHours;
@JsonProperty("blockTime")
private int blockTime;
@JsonProperty("availableGroup")
private List<Integer> availableGroup;
}

[Entity]Root object to encapsulate column in table row

import com.vladmihalcea.hibernate.type.json.JsonBinaryType;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import javax.persistence.*;

@Data
@Entity
@Table(name = "my_data", schema = "shared")
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class MyData {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
private Long id;

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private MyConfig myConfig;
}

Repository layer

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MyDataRepo extends JpaRepository<MyData, Long> {
}

Service layer

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class MyDataService {

@Autowired
private MyDataRepo myDataRepo;

public List<MyData> getAllMyspecificData(){
    List<MyData> allMyData = myDataRepo.findAll();
    return allMyData;
}
 }

REST End point

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping(path = "/my")
public class MyResouce {
@Autowired
MyDataService myDataService;

@GetMapping("/data")
public ResponseEntity<Object> getAllMyData() {
     List<MyData> myDataList = 
     myDataService.getAllMyspecificData();

    return new ResponseEntity<>(myDataList, HttpStatus.OK);
}
}

Answer 3

Sharing my own example as I struggled decomposing the provided answers for my specific needs. Hopefully this helps others. My examples are in groovy and I'm integrating with a postgres SQL database. This is a simple example of how to search a JSON column on a field called "name" and use paging.

JSON support class

@TypeDefs([@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)])
@MappedSuperclass
class JSONSupport {}

The entity class:

@Entity
@Table(name = "my_table")
class MyEntity extends JSONSupport {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long pk

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    String jsonData
}

The specification class

class NameSpecification implements Specification<MyEntity> {

    private final String name

    PhoneNumberSpecification(String name) {
        this.name = name
    }

    @Override
    Predicate toPredicate(Root<ContactEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.equals(
                   builder.function(
                       "jsonb_extract_path_text",
                       String.class,
                       root.<String>get("jsonData"),
                       builder.literal("name")
                   ),
                   this.name
              )
    }
}

The repository

interface MyEntityRepository extends PagingAndSortingRepository<MyEntity, Long>, JpaSpecificationExecutor<MyEntity> {}

The usage

@Service
class MyEntityService {
    
    private final MyEntityRepository repo
    
    MyEntityService(MyEntityRepository repo) {
         this.repo = repo
    }    

    Page<MyEntity> getEntitiesByNameAndPage(String name, Integer page, Integer pageSize) {
        PageRequest pageRequest = PageRequest.of(page, pageSize, Sort.by("pk"))

        NameSpecification spec = new NameSpecification(name)
        return repo.findAll(spec, pageRequest)
    }
}

Answer 4

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

Answer 5

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 6

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 7

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)