Re: Better way to sort a JSONB array?

On Mon, Aug 7, 2017 at 2:12 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

> 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'
>>
>>
>> Can you provide the final structure of json_table2 in DDL or similar?

If "->" isn't right (b/c it's an array), would "->>[n]" work (if you know
the array element you want to sort by - presumably "0")? That would sort by
the first element in the array.

I'm just confused about your question (and knowing you know a ton about
Postgres), as to why, once you have a stable table structure in
json_table2, why you can't just use standard sql and json operators to
pinpoint the data you want and order on it..