Re: [SQL] Better way to sort a JSONB array? - Mailing list pgsql-sql

From David G. Johnston
Subject Re: [SQL] Better way to sort a JSONB array?
Date
Msg-id CAKFQuwYPf1oQV0tK-=WMev=1seUn_nWZDV74WOgApoN2YqLjLA@mail.gmail.com
Whole thread Raw
In response to Re: [SQL] Better way to sort a JSONB array?  (Michael Moore <michaeljmoore@gmail.com>)
Responses Re: [SQL] Better way to sort a JSONB array?  (Michael Moore <michaeljmoore@gmail.com>)
List pgsql-sql
On Mon, Aug 7, 2017 at 2:53 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
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. 


​PostgreSQL is closer to "pure function​al" than "object oriented" if that helps explain things.  We do a thorough job of documenting the functions we do have and a quick skim of the json related functions does not reveal any with a signature like:  jsonb_sort(array_of_objects_json_array_value jsonb, top_level_object_key_name__or_path text).  You might want to post that as a feature request on -general and see what others more familiar with the json implementation think about the idea - we have jsonb_set and jsonb_insert so the request isn't completely novel -  but as it stands today the whole "dis-assemble->act->re-assemble" flow is the one that we are stuck with.  

David J.

pgsql-sql by date:

Previous
From: Michael Moore
Date:
Subject: Re: [SQL] Better way to sort a JSONB array?
Next
From: Michael Moore
Date:
Subject: Re: [SQL] Better way to sort a JSONB array?