Sorting on a nested field in a jsonb object whose parent key can be multiple values

Question

I have a field that is of type jsonb that is structured like:

data
{     "prices": {         "[parent key]": {             "price": 20         }     } }

[parent key] can be 5 possible values but all I really need is the price field. Ultimately I would like to extract that price field as well as order the results by that field. Is there a way that this can be done?

Example: I would like to get the price for each entry in descending order according to price.

create table my_table(
  id int generated by default as identity primary key,
  data jsonb);
insert into my_table select * from jsonb_populate_recordset(null::my_table,
'[{
  "id": 1,
  "data": {
    "prices": {
      "x": {"price": 20}
    }
  }
},
{
  "id": 2,
  "data": {
    "prices": {
      "y": {"price": 86}
    }
  }
},
{
  "id": 3,
  "data": {
    "prices": {
      "z": {"price": 21},
      "b": {"price": 41}
    }
  }
}]');

If an entry has more than one parent key for 'price', select the larger of the two, returning:

[
  {
    "id": 2,
    "price": 86
  },
  {
    "id": 3,
    "price": 41
  },
  {
    "id": 1,
    "price": 20
  }
]

Answer 1

1. distinct on(id)..order by id,price desc instructs Postgres to pick one value per id, selecting it by the highest price.
2. data @? $.prices.*.price will use the index on data column to avoid processing rows that don't have that price key.
3. jsonb_path_query(data,'$.prices.*.price') already mentioned by @jjanes uses a JSONPath * wildcard where you're unsure what the parent key of price is. It returns all matches from each row, out of which distinct on will pick the one, highest price.

If you mean to get that one long literal jsonb array as output, aggregate with jsonb_agg(). Using to_jsonb() you can automatically map entire rows of the subquery to objects in that array and have it internally sorted by price:
_{demo at db<>fiddle}

select jsonb_agg(to_jsonb(subquery)order by price desc)
from(select distinct on(id)id
           ,jsonb_path_query(data,'$.prices.*.price')as price
     from your_table 
     where data @? '$.prices.*.price'
     order by id,price desc)as subquery;

[     {         "id": 2,         "price": 86     },     {         "id": 3,         "price": 41     },     {         "id": 1,         "price": 20     } ]

Otherwise, if you were showing a dict as returned by the client library while on SQL level those are supposed to be separate rows:

select id,price
from(select distinct on(id)id
           ,jsonb_path_query(data,'$.prices.*.price')as price
     from your_table 
     where data @? '$.prices.*.price'
     order by id,price desc)as subquery
order by price desc;

id	price
2	86
3	41
1	20