How to reorder array in JSONB type column

Question

In PostgreSQL table, a JSONB type column, and the value stored inside is an array [3,6,78,1].

What should I do to reorder it like [1,3,6,78]?

Answer 1

Unnest the array with jsonb_array_elements() and aggregate its sorted elements using jsonb_agg():

with the_data(val) as (values ('[3,6,78,1]'::jsonb))

select jsonb_agg(elem order by elem) as val
from the_data
cross join lateral jsonb_array_elements(val) as arr(elem);

      val      
---------------
 [1, 3, 6, 78]
(1 row)

You can use the query in a custom function which will be handy in more complex queries:

create or replace function jsonb_sort_array(jsonb)
returns jsonb language sql immutable
as $$
    select jsonb_agg(elem order by elem)
    from jsonb_array_elements($1) as arr(elem)
$$;

with the_data(val) as (values ('[3,6,78,1]'::jsonb))

select jsonb_sort_array(val) as val
from the_data;

      val      
---------------
 [1, 3, 6, 78]
(1 row)