Sort PostgreSQL JSONB object by object key's value

Question

Given the following JSON (or JSONB) object:

{"apple": 13.1, "banana": 2.0, "zucchini": 7.2, "orange": 19.1}

I would like to sort the object keys by their values and get this instead:

{"banana": 2.0, "zucchini": 7.2, "apple": 13.1, "orange": 19.1}

How do I do that using PostgreSQL 14 and its JSON/JSON-B features?

Answer 1

Type json can hold an arbitrary order of keys but jsonb deduplicates and reorders the keys internally. That means you will need json and you will need to construct it based on a reordered json(b)_each_text() of the input value, which you need to aggregate back into json_object_agg(), internally ordering it by values: demo at db<>fiddle

select json_object_agg(k,v order by v::numeric)
from my_table
cross join json_each_text(jdata) as j(k,v)
group by my_table.ctid;

json_object_agg
{ "banana" : "2.0", "zucchini" : "7.2", "apple" : "13.1", "orange" : "19.1" }

I don't know your primary key, so to re-construct each json(b) value in your desired order I grouped them by system column ctid, which I could rely on being always present and unique.