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

Question

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?

The part {texts = ...} isn't valid JSON. If that is the intended output, then please also show us the input this is based on. — user330315
– user330315, Commented Mar 18, 2021 at 11:48

Abelisto · Accepted Answer · 2021-03-18 13:08:44Z

1

The algorythm is simple:

Decompose array to row set
Sort row set
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

answered Mar 18, 2021 at 13:08

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related