Postgres order by price lowest to highest с использованием массива jsonb

Предположим, у меня есть следующая схема продукта, которая имеет общие свойства, такие как название и т. д., а также варианты в массиве.

Как я буду заказывать продукты по цене от самой низкой до самой высокой?

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}"
1 2
sql postgresql jsonb

1 ответ:

Использование jsonb_array_elements() to 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)
3