postgresql使用jsonb数组按价格从低到高排序

Question

假设我有以下产品模式，它具有常见属性，如标题等，以及数组中的变体。

我该如何按照从低到高的价格来订购产品呢?

drop table if exists product;
create table product (
  id int,
  data jsonb
);
insert into product values (1, '
{
  "product_id": 10000,
  "title": "product 10000",
  "variants": [
    { 
      "variantId": 100,
      "price": 9.95,
      "sku": 100,
      "weight": 388
    },
    { 
      "variantId": 101,
      "price": 19.95,
      "sku": 101,
      "weight": 788
    }
  ]
}');
insert into product values (2, '
{
  "product_id": 10001,
  "title": "product 10001",
  "variants": [
    { 
      "variantId": 200,
      "price": 89.95,
      "sku": 200,
      "weight": 11
    },
    { 
      "variantId": 201,
      "price": 99.95,
      "sku": 201,
      "weight": 22
    }
  ]
}');
insert into product values (3, '
{
  "product_id": 10002,
  "title": "product 10002",
  "variants": [
    { 
      "variantId": 300,
      "price": 1.00,
      "sku": 300,
      "weight": 36
    }
  ]
}');

select * from product;
1;"{"title": "product 10000", "variants": [{"sku": 100, "price": 9.95, "weight": 388, "variantId": 100}, {"sku": 101, "price": 19.95, "weight": 788, "variantId": 101}], "product_id": 10000}"
2;"{"title": "product 10001", "variants": [{"sku": 200, "price": 89.95, "weight": 11, "variantId": 200}, {"sku": 201, "price": 99.95, "weight": 22, "variantId": 201}], "product_id": 10001}"
3;"{"title": "product 10002", "variants": [{"sku": 300, "price": 1.00, "weight": 36, "variantId": 300}], "product_id": 10002}"

Answer 1

使用jsonb_array_elements()来unnest variants，例如:

select 
    id, data->'product_id' product_id, 
    var->'sku' as sku, var->'price' as price
from 
    product, jsonb_array_elements(data->'variants') var
order by 4;
 id | product_id | sku | price 
----+------------+-----+-------
  3 | 10002      | 300 | 1.00
  1 | 10000      | 100 | 9.95
  1 | 10000      | 101 | 19.95
  2 | 10001      | 200 | 89.95
  2 | 10001      | 201 | 99.95
(5 rows)