如何为PostgreSQL查询函数编写Spring Data JPA Specification？

场景与需求 #

我在PostgreSQL中有一张名为pet的表，包含JSONB类型的details列，表结构及数据如下：

需求是编写Spring Data JPA Specification，过滤details列中以Colour结尾的属性名且属性值为指定值（如red）的记录。我已通过PostgreSQL查询实现该逻辑：

select * from pet 
where exists (
    select from jsonb_each_text(details) as kv 
    where kv.key like '%Colour' 
    and kv.value = 'red');

但编写对应的JPA Specification时遇到困难，尝试的代码如下：

尝试的Specification代码 #

(root, criteria, builder) -> {
    Expression<Tuple> function = 
        builder.function("jsonb_each_text", Tuple.class, root.get("details"));
            
    // 不确定是否需要设置别名，就像SQL里那样
    var kv = function.alias("kv");

    var subQuery = builder.createQuery().subquery(Tuple.class).select(function);

    // 不知道该用什么作为root，Tuple不是实体，直接用会报错
    var functionRoot = criteria.from(Tuple.class); 

    return builder.exists(subQuery.where(builder.like(functionRoot.get("key"), "%Colour"), builder.equal(functionRoot.get("value"), "red")));
};

Pet实体类定义 #

import com.vladmihalcea.hibernate.type.json.JsonType;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import java.util.Collections;
import java.util.Map;
import java.util.UUID;

@Getter
@Setter
@Entity
@TypeDef(name = "json", typeClass = JsonType.class)
public class Pet {

    @Id
    @GeneratedValue
    private UUID id;

    private String name;

    @Type(type = "json")
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> details = Collections.emptyMap();

}

错误分析与解决指引 #

你的代码存在的核心问题 #

1. 子查询创建方式错误：直接用builder.createQuery()创建子查询，未关联主查询的Pet Root，导致子查询与主表无关联，无法实现exists的逻辑。
2. 错误使用Tuple作为Root：Tuple是JPA封装查询结果的类型，不能作为Criteria查询的Root，因为它不是实体类，无法映射到数据库表。
3. 未正确处理jsonb_each_text的返回结果：jsonb_each_text返回行级键值对集合，需要在子查询中关联主表，并正确提取键值对的key和value字段。

修正后的实现代码 #

以下是符合需求的Specification实现：

import javax.persistence.criteria.*;
import java.util.UUID;

public class PetSpecifications {

    public static Specification<Pet> hasColourPropertyWithValue(String value) {
        return (root, query, builder) -> {
            // 创建子查询，返回Pet的UUID类型，用于关联主查询
            Subquery<UUID> subQuery = query.subquery(UUID.class);
            Root<Pet> subRoot = subQuery.from(Pet.class);
            
            // 调用PostgreSQL的jsonb_each_text函数，展开details的键值对
            Expression<Tuple> kvPairs = builder.function(
                "jsonb_each_text",
                Tuple.class,
                subRoot.get("details")
            );
            
            // 提取键值对的key和value字段（通过函数获取）
            Expression<String> key = builder.function(
                "jsonb_object_field_text",
                String.class,
                kvPairs,
                builder.literal("key")
            );
            Expression<String> valueExpr = builder.function(
                "jsonb_object_field_text",
                String.class,
                kvPairs,
                builder.literal("value")
            );
            
            // 子查询的过滤条件：key以Colour结尾，value匹配指定值
            Predicate subPredicate = builder.and(
                builder.like(key, "%Colour"),
                builder.equal(valueExpr, value)
            );
            
            // 子查询选择主表的id，并与主查询的id关联，确保是同一条记录
            subQuery.select(subRoot.get("id"))
                   .where(
                       builder.equal(subRoot.get("id"), root.get("id")),
                       subPredicate
                   );
            
            // 主查询条件：存在满足子查询条件的记录
            return builder.exists(subQuery);
        };
    }
}

关键说明 #

• 子查询关联主表：通过subRoot.get("id")和root.get("id")的相等条件，确保子查询只针对当前主表记录的details字段展开。
• 正确提取键值对：使用jsonb_object_field_text函数从jsonb_each_text返回的Tuple中提取key和value，这是PostgreSQL支持的JSON操作函数。
• 类型匹配：子查询返回UUID类型（Pet的主键类型），与主查询的主键类型一致，保证关联逻辑正确。

替代方案（简化逻辑） #

如果你的Hibernate版本支持，也可以先获取所有以Colour结尾的键，再检查对应值，代码如下：

public static Specification<Pet> hasColourPropertyWithValue(String value) {
    return (root, query, builder) -> {
        // 获取details中所有以Colour结尾的键
        Expression<String> keysExpr = builder.function(
            "jsonb_object_keys",
            String.class,
            root.get("details")
        );
        // 过滤键，并检查对应值是否匹配
        Predicate keyMatch = builder.like(keysExpr, "%Colour");
        Predicate valueMatch = builder.equal(
            builder.function(
                "jsonb_extract_path_text",
                String.class,
                root.get("details"),
                keysExpr
            ),
            value
        );
        return builder.and(keyMatch, valueMatch);
    };
}

这个方案不需要子查询，逻辑更简洁，但需要确保Hibernate能正确解析jsonb_object_keys和jsonb_extract_path_text函数。

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