Re: [SQL] Better way to sort a JSONB array? - Mailing list pgsql-sql
From | Michael Moore |
---|---|
Subject | Re: [SQL] Better way to sort a JSONB array? |
Date | |
Msg-id | CACpWLjPfnNM8bgSFULoDjSMPjF5hXhv92uMNaqpauLGt=-8hEg@mail.gmail.com Whole thread Raw |
In response to | Re: [SQL] Better way to sort a JSONB array? (Steve Midgley <science@misuse.org>) |
Responses |
Re: [SQL] Better way to sort a JSONB array?
(Michael Moore <michaeljmoore@gmail.com>)
|
List | pgsql-sql |
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@misuse.org> wrote:
On Mon, Aug 7, 2017 at 2:12 PM, Michael Moore <michaeljmoore@gmail.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]loopjson_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.thanksOn Mon, Aug 7, 2017 at 1:51 PM, Steve Midgley <science@misuse.org> wrote:On Mon, Aug 7, 2017 at 1:13 PM, Michael Moore <michaeljmoore@gmail.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 "->"? 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_tableorder 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..