How to order json_agg by value of numeric field

Question

I have the following query

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
$$ LANGUAGE sql
STABLE
STRICT
PARALLEL SAFE

It is executed like this and returns this data.

SELECT "_key" k,"get_set_members"("_key") m
  FROM UNNEST($1::TEXT[]) "_key";`

{
  k: 'nameofkey',
  m: [
    { value: 'v1', score: 1 },
    { value: 'v2', score: 2 },
    { value: 'v3', score: 3 }
  ]
}

I am trying to order the elements of the m array by the value of the score field. I have tried adding

ORDER BY item->>'score' ASC

at the end but that didn't work. Basically I want to be able to reverse the order so it can be DESC/ASC based on the score field.

Answer 1

Imposing a certain order on a JSON is odd, because JSON does not know an order. However, as long as you stick with the data type json, you can get it with

json_agg(json_build_object(...) ORDER BY ...)