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