Обсуждение: [SQL] Better way to sort a JSONB array?

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_table

order by json_field->'portal_name'

Steve

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]

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

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]

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

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:

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.

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.