Re: Better way to sort a JSONB array?

json_table2 is declared as a variable in pgsql. It is not a jsonb table
column. It is loaded from a statement like:
execute 'select jsonb_agg(row_to_json(zzz)) from (...extremely complex
query goes here) zzz' into json_table2;

Later, additional rows are added like:
for a in select * from [table with the same columns as json_table_2]
loop
json_table2 = coalesce (json_table2 || jsonb_agg(row_to_json (a)),
jsonb_agg(row_to_json (a)) );
end loop;

So, because of the fact that the json_table2 is appended to in a random
order, json_table2 now must be sorted.
thanks

On Mon, Aug 7, 2017 at 1:51 PM, Steve Midgley <science(at)misuse(dot)org> wrote:

> On Mon, Aug 7, 2017 at 1:13 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
> wrote:
>
>> This works, but surely there is a better way to do it:
>>
>> select jsonb_agg(row_to_json(alias)) from
>> (Select * from jsonb_populate_recordset(null::tx_portal,
>> json_table2) order by portal_name) alias
>> into
>> json_table2;
>>
>> It sorts the json_table2 array in "portal_name" order.
>>
>
> I'm confused why you can't just pull portal_name from the json structure
> using "->"
> <https://www.postgresql.org/docs/current/static/functions-json.html>? For
> example, assuming your json structure that looks like:
>
> {"portal_name": "some data.."}
>
> And a table that looks like:
>
> | id | json_field |
>
> Wouldn't this sql work:
>
> select * from json_table
> order by json_field->'portal_name'
>
> Steve
>