postgresql - Can't sort by all array's items?

Question

Postgresq 9.6

json

 "availability": [
      {
        "qty": 25,
        "price": 1599,
        "is_available": true
      },
      {
        "qty": 72,
        "price": 3599,
        },
         "is_available": true
   ]

table with column data. Type is jsonb

If I want to sort first array's(availability) item by field "price" I this:

SELECT * 
from product prod
WHERE to_tsvector('english', prod.data) @@ to_tsquery('gram')
ORDER BY prod.data #> '{availability,0,price}' desc

OK.

But I need to sort all fields "price" in array availability

Smt like this (pseudo code)

SELECT * 
    from product prod
    WHERE to_tsvector('english', prod.data) @@ to_tsquery('gram')
    ORDER BY prod.data #> '{availability,*,price}' desc

I need to to order by "price" desc.

The result must be

First record of result is second json

"availability": [
      {
        "qty": 25,
        "price": 11599,
        "is_available": true
      },
      {
        "qty": 72,
        "price": 13599,
        },
         "is_available": true
     ]
...
"availability": [
      {
        "qty": 25,
        "price": 1599,
        "is_available": true
      },
      {
        "qty": 72,
        "price": 3599,
        },
         "is_available": true
     ]

Is it possible?

标签： postgresql

Answer 1

这可以这样做：

select id, 
       jsonb_set(data, '{availability}', 
                        (select jsonb_agg(item order by (item ->> 'price')::numeric)
                         from jsonb_array_elements(data -> 'availability') as x(item))
                 ) as data
from product
where ...