How to control order of fields in jsonb_object_agg

Question

I am trying to build a jsonb object with keys ordered alphabetically. However it seems jsonb_object_agg(k, v) disregards the order of inputs and sorts the input by key length and then alphabetically.

E.g.

select jsonb_object_agg(k, v order by k) from (
    values ('b', 'b_something'), ('ab', 'ab_something')
) as t (k,v)

gives

{
  "b": "b_something",
  "ab": "ab_something"
}

but what I need is

{
  "ab": "ab_something"
  "b": "b_something",
}

Is there a way to achieve this?

Context I am flattening a json column, where the contents follow a uniform but unwieldy schema. I have succeeded in doing so thanks to this useful answer, but the order of the keys is not how I need them.

["By contrast, `jsonb` does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys."](https://www.postgresql.org/docs/current/datatype-json.html) So if you want to preserve the order of keys then use `json_object_agg()` instead. — Abelisto, Mar 27 '21 at 09:38

score 5 · Accepted Answer · edited Jun 19 '23 at 11:04

jsonb stores the data in a parsed form that makes accessing its parts efficient. As a consequence, the formatting of the original string is not preserved, nor is the order of attributes, nor can you have duplicate attributes. So what you are asking for is impossible.

You could use the json data type, which is essentially a string with syntax check that preserves the original order.

To establish an order during aggregation, use json_agg(... ORDER BY ...).

How to control order of fields in jsonb_object_agg

1 Answers1

Linked