如何用Spring Data JPA CriteriaQuery实现PostgreSQL JSONB数组查询

错误原因 #

1. JSONB键访问方式错误：你直接通过subCustomerDataPath.get(key)尝试访问JSONB字段的键，JPA会将其视为实体属性而非JSON内部的键，导致路径解析失败，出现Illegal attempt to dereference path source [null.customerData]错误。
2. 子查询未关联主查询：你的子查询重新从Customer表查询，没有与主查询的Customer行建立关联，不符合原SQL中EXISTS子查询依赖主表行的逻辑。
3. 查询结果与原SQL不一致：原SQL返回的是Order的id，但你的代码中cq.select(cRoot.get("id"))返回的是Customer的id，需要修正以匹配需求。

修正后的CriteriaBuilder实现代码 #

public List<Long> findOrderIds(EntityManager entityManager, String key, List<String> sidValues) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    Root<Customer> cRoot = cq.from(Customer.class);
    // 关联Customer与Order，对应原SQL的INNER JOIN
    Join<Customer, Order> oJoin = cRoot.join("orders", JoinType.INNER);

    // 构建关联子查询，绑定主查询的Customer行
    Subquery<Integer> subquery = cq.subquery(Integer.class);
    Root<Customer> subRoot = subquery.from(Customer.class);

    // 1. 提取JSONB字段中指定键对应的数组：customer_data->'sid'
    Path<Object> jsonArray = cb.function(
        "jsonb_extract_path_text",
        Object.class,
        subRoot.get("customerData"),
        cb.literal(key)
    );
    // 2. 将JSON数组展开为行：jsonb_array_elements_text(...)
    Path<String> arrayElement = cb.function(
        "jsonb_array_elements_text",
        String.class,
        jsonArray
    );

    // 子查询条件：数组元素匹配指定值，同时关联主查询的Customer ID
    subquery.select(cb.literal(1))
            .where(
                cb.equal(subRoot.get("id"), cRoot.get("id")),
                arrayElement.in(sidValues.stream().map(String::toUpperCase).toList())
            );

    // 主查询：选择Order的ID，满足子查询存在匹配
    cq.select(oJoin.get("id"))
      .where(cb.exists(subquery));

    return entityManager.createQuery(cq).getResultList();
}

关键修正点说明 #

• JSONB字段处理：用jsonb_extract_path_text函数提取指定键的JSON数组，再用jsonb_array_elements_text将数组展开为多行，完全对应原SQL的jsonb_array_elements_text(c.customer_data->'sid')逻辑。
• 关联子查询：通过cb.equal(subRoot.get("id"), cRoot.get("id"))绑定子查询与主查询的Customer行，确保EXISTS检查针对当前主表行的JSON数据。
• 匹配条件优化：用in()替代多个OR条件，代码更简洁，同时保留大写匹配逻辑。
• 结果修正：将返回结果改为Order的id，与原SQL的SELECT o.id保持一致（若实际需要返回Customer的id，可改为cRoot.get("id")）。

替代实现：使用@Query原生SQL #

如果CriteriaBuilder实现过于繁琐，可直接用Spring Data JPA的@Query注解编写原生SQL，更直观：

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query(value = """
        SELECT o.id
        FROM customer c 
        INNER JOIN "order" o ON c.id = o.c_id 
        WHERE EXISTS (
            SELECT 1
            FROM jsonb_array_elements_text(c.customer_data->:key) AS value
            WHERE UPPER(value) IN :sidValues
        )
        """, nativeQuery = true)
    List<Long> findOrderIdsByCustomerDataSid(@Param("key") String key, @Param("sidValues") List<String> sidValues);
}

注意：PostgreSQL中order是关键字，表名需要用双引号包裹（"order"）。

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