如何按numeric字段score的值对json_agg结果进行排序？

你之前添加的ORDER BY item->>'score' ASC无效，原因是item是聚合后生成的JSON数组对象，此时排序操作无法作用于数组内部的元素。正确的做法是在聚合前对关联后的行按score字段排序，让json_agg按照排序后的行顺序构建数组。

修改后的固定排序函数（升序） #

CREATE FUNCTION "get_set_members"(TEXT) RETURNS JSON AS $$
    SELECT json_agg(json_build_object('value', z."value", 'score', z."score")) as item
      FROM "legacy_object_live" o
     INNER JOIN "legacy_zset" z
             ON o."_key" = z."_key"
            AND o."type" = z."type"
          WHERE o."_key" = $1
      ORDER BY z."score" ASC -- 聚合前对score字段升序排序
$$ LANGUAGE sql
STABLE
STRICT
PARALLEL SAFE

如果需要支持动态切换升序/降序，可以给函数增加一个排序方向参数：

支持动态排序方向的函数 #

CREATE FUNCTION "get_set_members"(TEXT, TEXT) RETURNS JSON AS $$
    SELECT json_agg(json_build_object('value', z."value", 'score', z."score")) as item
      FROM "legacy_object_live" o
     INNER JOIN "legacy_zset" z
             ON o."_key" = z."_key"
            AND o."type" = z."type"
          WHERE o."_key" = $1
      ORDER BY z."score" CASE WHEN $2 = 'DESC' THEN DESC ELSE ASC END
$$ LANGUAGE sql
STABLE
STRICT
PARALLEL SAFE

执行示例（动态排序版本） #

-- 按score升序查询
SELECT "_key" k,"get_set_members"("_key", 'ASC') m
  FROM UNNEST($1::TEXT[]) "_key";

-- 按score降序查询
SELECT "_key" k,"get_set_members"("_key", 'DESC') m
  FROM UNNEST($1::TEXT[]) "_key";

这样生成的m数组就会按照score字段的值正确排序。

内容的提问来源于stack exchange，提问作者Barış Uşaklı