如何通过Specification与Criteria Builder实现Postgres JSONB数值排序

问题背景 #

我在PostgreSQL中有一张带jsonb列的表，需要按JSON中指定键的数值对查询结果排序。原生SQL可以实现如下：

SELECT key->>'value' FROM table ORDER BY CAST(key->>'value' AS DECIMAL);

但无法直接通过Specification实现这个需求，于是封装了一个PL/pgSQL函数处理类型转换：

CREATE OR REPLACE FUNCTION cast_number(str varchar)
RETURNS REAL
LANGUAGE plpgsql
AS
$$
DECLARE 
new_number REAL;
BEGIN
 SELECT CAST(str AS DECIMAL) INTO new_number;
 RETURN new_number;
END;
$$

之后在Specification工厂构建排序子句时，通过CriteriaBuilder调用这个函数，同时调用jsonb_extract_path_text提取JSON字段值，现在想了解该方案的性能表现。

性能分析 #

现有方案的潜在问题 #

• PL/pgSQL函数的额外开销：PL/pgSQL属于过程化语言，每次调用都会产生上下文切换开销，批量处理时相比直接写CAST语句，性能差距会被放大。
• 无法利用索引：无论直接用CAST(key->>'value' AS DECIMAL)还是通过自定义函数，PostgreSQL默认无法为这类表达式创建索引，大表场景下排序会触发全表扫描+排序，性能急剧下降。
• 冗余类型转换：函数中先将字符串转成DECIMAL再存入REAL变量，多了一次不必要的类型转换，增加了计算成本。

优化建议 #

1. 简化自定义函数（若必须使用） #

改用更轻量的SQL函数，同时标记为IMMUTABLE（告诉PostgreSQL函数返回值仅依赖输入，无副作用），减少性能开销：

CREATE OR REPLACE FUNCTION cast_number(str varchar)
RETURNS REAL
LANGUAGE sql
IMMUTABLE
AS $$
 SELECT CAST(str AS REAL);
$$;

2. 创建表达式索引（核心优化） #

如果频繁按该JSON字段数值排序，创建表达式索引是提升性能的关键：

-- 针对直接CAST的场景
CREATE INDEX idx_table_json_value_num ON table (CAST(key->>'value' AS REAL));

-- 针对使用自定义函数的场景
CREATE INDEX idx_table_json_value_num ON table (cast_number(key->>'value'));

有了索引后，排序操作可直接利用索引的有序性，避免全表排序。

3. 在Specification中直接构造CAST表达式 #

其实无需自定义函数，可通过CriteriaBuilder直接构造CAST逻辑，示例如下：

// 假设实体根为root，jsonb列名为"key"，目标JSON键为"value"
Expression<String> jsonValue = criteriaBuilder.function(
    "jsonb_extract_path_text",
    String.class,
    root.get("key"),
    criteriaBuilder.literal("value")
);
Expression<Double> numericValue = criteriaBuilder.function(
    "CAST",
    Double.class,
    jsonValue,
    criteriaBuilder.literal("REAL")
);
orderBy = criteriaBuilder.orderBy(criteriaBuilder.asc(numericValue));

这样就能在Specification中实现原生SQL的CAST(key->>'value' AS REAL)逻辑，省去函数调用的额外开销。

总结 #

• 若必须用自定义函数，优先选择SQL函数并标记IMMUTABLE，降低性能损耗。
• 大表场景下，创建表达式索引是提升排序性能的核心手段。
• 尽量尝试在Specification中直接构造CAST表达式，避免函数调用的额外开销。

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