SQL中如何依据字段类型对JSON值实现正确排序？

我有两张数据表：

attribute表 #

id          project_id  name          type
"556e28ab"  "14b106fd"  "running_id"  "INTEGER"
"7c78e6a4"  "14b106fd"  "headline"    "TEXT"

record表 #

id          project_id  data
"63d7af84"  "14b106fd"  "{\"headline\": \"Mike Tyson set to retire after loss\", \"running_id\": 0}"
"055e85ff"  "14b106fd"  "{\"headline\": \"Iraqi vote remains in doubt\", \"running_id\": 1}"
"2dd0acdc"  "14b106fd"  "{\"headline\": \"Conservatives Ponder Way Out of Wilderness\", \"running_id\": 2}"
"d8b82916"  "14b106fd"  "{\"headline\": \"Final report blames instrument failure for Adam Air Flight 574 disaster\", \"running_id\": 3}"
"a71268cf"  "14b106fd"  "{\"headline\": \"Kim Sets a Skating Record and Wins Her First World Title\", \"running_id\": 4}"

尝试按data字段JSON内的running_id排序时，直接用r.data ->>'running_id'会被识别为字符串类型，导致排序不符合预期：

SELECT *
FROM record r
WHERE r.project_id = '14b106fd'
ORDER BY (r.data ->>'running_id') ASC;

显式转换类型虽然可行，但无法支持任意JSON字段排序（字段由用户定义）。尝试通过attribute表的类型信息做CASE转换，但结果仍被识别为字符串类型：

SELECT r.*, a.data_type
FROM record r
JOIN attribute a ON (a.name = 'running_id' AND a.project_id = r.project_id)
WHERE r.project_id = '14b106fd'
ORDER BY
    CASE CAST(a.data_type AS TEXT)
        WHEN 'INTEGER' THEN CAST(r.data ->> 'running_id' AS INTEGER)
        WHEN 'NUMERIC' THEN CAST(r.data ->> 'running_id' AS NUMERIC)
        ELSE r.data ->>'running_id'
    END;

需要一种通用方案，依据字段类型对JSON值做正确排序。

方案1：利用jsonb原生类型自动排序 #

如果你的data字段是jsonb类型（推荐优先使用，性能优于json），直接使用->操作符获取原生JSON值，PostgreSQL会自动根据JSON内部的实际类型（数字、字符串等）完成正确排序：

SELECT *
FROM record r
WHERE r.project_id = '14b106fd'
ORDER BY (r.data -> 'running_id') ASC;

说明：如果当前data是json类型，可以先转换为jsonb再操作：(r.data::jsonb -> 'running_id')。

方案2：结合attribute表用动态SQL实现通用排序 #

如果必须基于attribute表的类型定义，且要支持任意自定义字段，可以通过动态SQL自动生成类型转换逻辑。比如创建一个专用函数：

CREATE OR REPLACE FUNCTION sort_records_by_field(p_project_id text, p_field_name text)
RETURNS SETOF record AS $$
DECLARE
    v_field_type text;
BEGIN
    -- 从attribute表获取目标字段的类型
    SELECT type INTO v_field_type
    FROM attribute
    WHERE project_id = p_project_id AND name = p_field_name;

    -- 动态生成并执行排序SQL
    RETURN QUERY EXECUTE format(
        'SELECT *
         FROM record r
         WHERE r.project_id = %L
         ORDER BY CAST(r.data ->> %L AS %s) ASC',
        p_project_id, p_field_name, v_field_type
    );
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT * FROM sort_records_by_field('14b106fd', 'running_id');

说明：动态SQL会自动匹配attribute表的字段类型，完成JSON值的转换与排序，完全适配任意用户自定义字段。

方案3：修复CASE表达式的类型统一问题 #

之前的CASE表达式失效，是因为PostgreSQL要求CASE所有分支返回相同类型，会自动将数值类型转为字符串。可以通过给不同类型的排序值添加前缀标记，实现类型区分的排序：

SELECT r.*, a.type
FROM record r
JOIN attribute a ON a.name = 'running_id' AND a.project_id = r.project_id
WHERE r.project_id = '14b106fd'
ORDER BY
    CASE a.type
        WHEN 'INTEGER' THEN '0:' || (r.data ->> 'running_id')::integer::text
        WHEN 'NUMERIC' THEN '1:' || (r.data ->> 'running_id')::numeric::text
        ELSE '2:' || r.data ->> 'running_id'
    END ASC;

说明：通过前缀标记区分类型优先级，保证数值类型按数值规则排序，字符串类型按字符串规则排序，同时让CASE分支返回统一的文本类型。

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