Spring Data JPA Examples: Querying Data by Json Properties in PostgresSql

2 ways of Query data by Json’s property from a table with Json type column.

Talk is cheap. Just see the code.

init_Sql:

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    data JSONB
);

Entity:

@Entity
@Getter
@Setter
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @Column(columnDefinition = "jsonb")
    private String data;

}

Native SQL way Repository (way1):

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "SELECT * FROM employee WHERE data->>'department' = ?1", nativeQuery = true)
    List<Employee> findByDepartment(String department);
}

Specification way Repository (way2):

@Repository
public interface EmployeeSpecificationRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {
}

Specification:

public class EmployeeSpecification {

    public static Specification<Employee> hasDepartment(String department) {
        return…