使用JPA标准的CriteriaBuilder，为Postgres的jsonb列构建谓词。

Question

使用JPA标准的CriteriaBuilder，为Postgres的jsonb列构建谓词。

jpaspring-data-jpacriteria-apijsonbpostgresql-9.6

7

我需要为一个JSONB列添加谓词到我的现有谓词列表中。

实体：

@Entity
@Table(name = "a")
@TypeDefs({
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class),
})
public class EntityA {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "a_id_seq")
    @SequenceGenerator(sequenceName = "a_id_seq", allocationSize = 1, name = "a_id_seq")
    @Column(name = "id")
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "json")
    @Type(type = "jsonb")
    private Json json;

    private static class Json {
        private String name;

        private Integer number;

        private String random;
    }
}

规格：

    public Specification<EntityA> buildSpecification(Filter filter){

            return (root, query, cb) -> {
                        List<Predicate> predicates = new ArrayList<>();


            Expression<String> nameExpression = root.get("name");

        Predicate namePredicate = nameExpression.in(filter.getNames());
                            predicates.add(namePredicate);

//TODO add a predicate for JSONB here


                        return cb.and(predicates.toArray(new Predicate[0]));
                    };

我的输入将是一个 List jsonNames 或 List jsonNumbers，我想用这个输入构建 CriteriaBuilder.In，并获取任何匹配项。

筛选条件：

@Data
public class Filter {
    private List<String> names;

    private List<String> jsonNames;

    private List<Integer> jsonNumbers;
}

- Niv

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nikolai Shevchenko · Accepted Answer

对于PostgreSQL

Predicate inJsonNumbers = cb
        .function("jsonb_extract_path_text", 
                String.class, 
                root.get("json"), 
                cb.literal("number"))
        .in(filter.getJsonNumbers())

Predicate inJsonNames = cb
        .function("jsonb_extract_path_text", 
                String.class, 
                root.get("json"), 
                cb.literal("name"))
        .in(filter.getJsonNames())