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]?
Add a comment
|
1 Answer
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)