如何用JPA Criteria Builder构建PostgreSQL JSONB数组查询？

场景说明 #

你的PostgreSQL表结构如下：

需要筛选出data数组中包含sn="sn2"的行，原生SQL实现为：

select * from mytable where (exists(select 1 from jsonb_array_elements(data) obj where obj->>'sn'='sn2'));

以下是两种满足需求的实现方案：

一、用JPA Criteria Builder构建EXISTS谓词 #

完全通过Criteria Builder API实现，无需嵌入原生SQL。假设你的实体类为MyTable，data字段用Jackson的JsonNode映射JSONB类型（若用String类型需调整转换逻辑），代码示例：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<MyTable> query = cb.createQuery(MyTable.class);
Root<MyTable> root = query.from(MyTable.class);

// 构建关联主查询的子查询
Subquery<Integer> subquery = query.subquery(Integer.class);
Root<MyTable> subRoot = subquery.correlate(root);

// 调用PostgreSQL的jsonb_array_elements函数拆分JSONB数组
Expression<Object> jsonElements = cb.function(
    "jsonb_array_elements",
    Object.class,
    subRoot.get("data")
);

// 将拆分后的JSON元素转为可操作的Path，提取sn字段
Path<String> snPath = cb.treat(jsonElements, JsonNode.class).get("sn").asText();

// 子查询设置select 1，并添加sn值匹配条件
subquery.select(cb.literal(1))
        .where(cb.equal(snPath, "sn2"));

// 主查询添加EXISTS谓词
Predicate existsPredicate = cb.exists(subquery);

// 与已有谓词组合（如果有的话）
Predicate finalPredicate = cb.and(已有谓词, existsPredicate);
query.where(finalPredicate);

List<MyTable> result = entityManager.createQuery(query).getResultList();

二、将原生SQL谓词与已有谓词结合 #

如果觉得Criteria Builder的方式过于繁琐，可以直接嵌入原生SQL条件和已有谓词组合，常用方式有两种：

方式1：用SQLRestriction生成谓词 #

直接将原生条件转为Predicate，再和已有谓词组合：

// 生成JSONB筛选的原生Predicate
Predicate jsonbPredicate = cb.sqlRestriction(
    "exists(select 1 from jsonb_array_elements(data) obj where obj->>'sn' = ?1)",
    "sn2",
    String.class
);

// 组合已有谓词
Predicate finalPredicate = cb.and(已有谓词, jsonbPredicate);
query.where(finalPredicate);

方式2：混合JPQL与原生SQL #

如果使用JPQL查询，可以直接在JPQL语句中嵌入原生SQL条件：

String jpql = "SELECT t FROM MyTable t WHERE 已有JPQL条件 AND EXISTS(SELECT 1 FROM jsonb_array_elements(t.data) obj WHERE obj->>'sn' = :snValue)";

List<MyTable> result = entityManager.createQuery(jpql, MyTable.class)
    .setParameter("snValue", "sn2")
    .getResultList();

内容的提问来源于stack exchange，提问作者Murugesan