Sort array and create a JSON object for each element in the array

user_13628404 0
Viewed 51

In my database, I have a column that stores an array of elements, g.e, {"B", "A", "C"}. And I am trying to firstly sort this array alphabetically and then create a JSON from each element in the sorted array and then return it like the following:

{TEXTS : [{"TEXT":"A","sortOrder":1}, {"TEXT":"B","sortOrder":2}, {"TEXT":"C","sortOrder":3}]}

How can I achieve this?

postgresql sql json
1 Answers

The algorythm is simple:

  1. Decompose array to row set
  2. Sort row set
  3. Aggregate row set into JSON object
select json_build_object('TEXTS', json_agg(t.*))
from (
    select "TEXT", row_number() over (order by "TEXT") as "sortOrder"
    from unnest('{B,A,C}'::text[]) as "TEXT") as t;

demo

user_5931440
Related