Re: Better way to sort a JSONB array?

Hi David,
Maybe there isn't a less verbose way. It just seems like a bit much that I
have to take the jsonb array, convert it into a relation so that SELECT can
operate on it, only so that I can sort it, and then convert it back into a
jsonb array object. I was hoping for something like
json_table2.aggSort("portal_name"). I'm not actually using json_table2 as a
name, that was just for my minimalist example. As mentioned, my real
project consists of refactoring a pgsql function which uses TEMP tables to
NOT use temp tables, but instead, JSONB objects.

Thanks !
Mike

On Mon, Aug 7, 2017 at 2:36 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> 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:
>>
>
> What do you mean by "better"?
> ​
>
>>
>> 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.
>>
>
> ​The only other possibility would be:
>
> CREATE TABLE json_table2 AS
> select jsonb_agg(row_to_json(alias) order by portal_name) from
> (select * from jsonb_populate_recordset(...)) alias​;
>
> i.e., move the order by into the aggregate; I prefer CREATE TABLE AS over
> SELECT INTO ...
>
> I don't know why you are thinking there is a better way...you are doing
> two distinct actions here and you have two invocations of select...seems
> like the minimal possible number of sub-statements to me. No one has
> written a c-language function for core that provides a more friendly
> interface to this. Looking at your query doing so using dynamic SQL within
> a pl/pgsql function would seem reasonably easy.
>
> I would suggest not using "json_table2" as both the name of the input
> variable and the target table name - it can be confusing to follow when you
> overload names like that.
>
> David J.
>
>