如何使用CriteriaQuery查询PostgreSQL中jsonb数组列的assignedPersonId字段

你的问题核心在于opinion_cells是jsonb数组类型，直接使用json_extract_path_text只会针对整个数组操作，而json_array_elements返回的是集合类型，无法直接用于普通的WHERE条件判断。下面提供两种可行的实现方式：

方案1：使用PostgreSQL的jsonb_contains函数（简洁高效） #

PostgreSQL的jsonb_contains（对应@>操作符）可以直接判断jsonb数组是否包含指定结构的元素，非常适合这种简单的匹配场景。

CriteriaQuery代码实现： #

// 假设你要筛选assignedPersonId为12的记录
String targetJson = "[{\"assignedPersonId\": 12}]";

Predicate matchPredicate = criteriaBuilder.equal(
    criteriaBuilder.function(
        "jsonb_contains", 
        Boolean.class,
        internalLetterRoot.get(InternalLetter_.OPINION_CELLS), // jsonb数组列
        criteriaBuilder.literal(targetJson) // 要匹配的jsonb片段
    ),
    Boolean.TRUE
);

criteriaQuery.where(matchPredicate);

原理说明： #

这个写法对应原生SQL：

SELECT * FROM internal_letter
WHERE opinion_cells @> '[{"assignedPersonId": 12}]'::jsonb;

@>操作符会检查jsonb数组中是否存在至少一个元素包含指定的键值对，性能优于展开数组的方式，因为可以利用jsonb的GIN索引（如果你给该列建了索引的话）。

方案2：使用EXISTS子查询（灵活支持复杂条件） #

如果需要更复杂的筛选逻辑（比如同时判断多个字段、范围查询等），可以通过构建EXISTS子查询来展开jsonb数组并筛选元素。

CriteriaQuery代码实现： #

// 1. 构建子查询
Subquery<Boolean> subquery = criteriaQuery.subquery(Boolean.class);
Root<InternalLetter> subRoot = subquery.correlate(internalLetterRoot);

// 2. 展开jsonb数组，得到每个元素
Expression<Object> jsonElements = criteriaBuilder.function(
    "jsonb_array_elements", 
    Object.class,
    subRoot.get(InternalLetter_.OPINION_CELLS)
);

// 3. 提取元素中的assignedPersonId并匹配目标值
Expression<String> assignedPersonId = criteriaBuilder.function(
    "json_extract_path_text", 
    String.class,
    jsonElements,
    criteriaBuilder.literal("assignedPersonId")
);

// 4. 子查询判断是否存在符合条件的元素
subquery.select(criteriaBuilder.literal(true))
        .where(criteriaBuilder.equal(assignedPersonId, "12"));

// 5. 主查询使用EXISTS条件
criteriaQuery.where(criteriaBuilder.exists(subquery));

原理说明： #

这个写法对应原生SQL：

SELECT * FROM internal_letter il
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(il.opinion_cells) elem
    WHERE elem->>'assignedPersonId' = '12'
);

通过子查询展开数组后，判断是否存在满足条件的元素，这种方式适合处理更复杂的筛选逻辑。

为什么你之前的写法失败？ #

• 第一种写法：直接对整个jsonb数组调用json_extract_path_text，只会返回null（因为数组本身没有assignedPersonId键），所以无法匹配任何记录。
• 第二种写法：json_array_elements返回的是集合类型，而CriteriaQuery的WHERE条件要求的是单值表达式，因此会抛出argument of OR must not return a set的错误，必须用EXISTS子查询来包装集合操作。

内容的提问来源于stack exchange，提问作者L.dev