Re: Better way to sort a JSONB array?

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(at)gmail(dot)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(at)misuse(dot)org> wrote:
>
>> On Mon, Aug 7, 2017 at 2:12 PM, Michael Moore <michaeljmoore(at)gmail(dot)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(at)misuse(dot)org>
>>> 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:
>>>>>
>>>>> 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 "->"
>>>> <https://www.postgresql.org/docs/current/static/functions-json.html>?
>>>> 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..
>>
>>
>