Re: Better way to sort a JSONB array?

As an aside, I had this entire pgsql function written using temp tables.
Now our DBA has decided to run this in a read-only database, so temp tables
are out. I'm trying to use jsonb in place of these temp tables. The output
of this function is the entire result set in a specific sort order.

Fun stuff! Back to the problem at hand, I think that ->> only allows one
to address the nth element of the array. I don't see how I would get from
there to sorting the entire jsonb array in portal_name order.

If I know a ton about Postgres, then Postgres must weight about 2000 tons.
Still very much to learn. :-)

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

> 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..
>
>