superlogical superlogical - 3 months ago 9
SQL Question

Postgres order by price lowest to highest using jsonb array

Say I have the following product schema which has common properties like title etc, as well as variants in an array.

How would I go about ordering the products by price lowest to highest?

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

Use jsonb_array_elements() to unnest variants, e.g.:

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)    
Comments