Home > mailing lists

Thread: [SQL] Better way to sort a JSONB array?

[SQL] Better way to sort a JSONB array?

From

Michael Moore

Date:

08 August 2017, 02:13:42

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.

Re: [SQL] Better way to sort a JSONB array?

From

Steve Midgley

Date:

08 August 2017, 02:51:37

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

Re: [SQL] Better way to sort a JSONB array?

From

Michael Moore

Date:

08 August 2017, 03:12:35

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'

Steve

Re: [SQL] Better way to sort a JSONB array?

From

Steve Midgley

Date:

08 August 2017, 03:17:49

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

Re: [SQL] Better way to sort a JSONB array?

From

Michael Moore

Date:

08 August 2017, 03:36:20

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

Re: [SQL] Better way to sort a JSONB array?

From

"David G. Johnston"

Date:

08 August 2017, 03:36:43

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.

Re: [SQL] Better way to sort a JSONB array?

From

Michael Moore

Date:

08 August 2017, 03:38:53

Almost forgot, here is the DDL on the json table structure.

CREATE TYPE ypxportal2__fgetquestions AS

(question_seq bigint,

prompt_seq bigint,

attribute_name character varying(150),

back_end_value character varying(1000),

display_text character varying(4000),

select_type character varying(30),

prompt_type_code character varying(50),

input_required character varying(1),

inacceptable_choice character varying(1),

answer_filter_flag character varying(1),

event_handler character varying(4000),

suppress_question_codename character varying(200),

all_questions_on_one_page_flag character varying(1),

attrib_values_of_question text,

qg_question_grp_key bigint,

qg_quest_set2tx_question_set bigint,

qg_lang2tx_question_language bigint,

qg_pvs2tx_portal_vendor_serv bigint,

qg_service2tx_service_catalog bigint,

qg_label character varying(100),

qg_form_name character varying(100),

qg_event_handler character varying(50),

qg_portal_ven2tx_portal_vendor bigint,

qg_portal2tx_portal bigint,

qg_vendor2tx_vendor bigint,

qg_dealer2tx_dealer bigint,

qg_edunamecode character varying(100),

qg_subjnamecode character varying(100),

qg_qualnamecode character varying(100),

question_prompt_type_code character varying(150),

question_key bigint,

score bigint,

qg_key2tx_criteria_tree bigint,

qg_state2tstateprovince character varying(2));

ALTER TYPE ypxportal2__fgetquestions

OWNER TO qsn_app;

On Mon, Aug 7, 2017 at 2:36 PM, Michael Moore <michaeljmoore@gmail.com> wrote:

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

Re: [SQL] Better way to sort a JSONB array?

From

Michael Moore

Date:

08 August 2017, 03:53:35

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.g.johnston@gmail.com> 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:

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.

Re: [SQL] Better way to sort a JSONB array?

From

"David G. Johnston"

Date:

08 August 2017, 04:04:49

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

Re: [SQL] Better way to sort a JSONB array?

From

Michael Moore

Date:

08 August 2017, 04:10:26

Thanks David, very nice explanation. Yes, functional vs object, got it. I feel like I can move forward without the feeling that I missed something obvious.

Regards,

Mike

On Mon, Aug 7, 2017 at 3:04 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

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