postgresqlaggregate-functionspostgresql-10

How to control order of fields in jsonb_object_agg

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.

Solution

  • 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 ...).