How do I get min of jsonb array-CodePudding

I have this

[{"bed": 0, "bath": 1, "price": [1768]}, {"bed": 0, "bath": 1, "price": [1824, 1824, 1828, 1869, 1869]}, {"bed": 1, "bath": 1, "price": [2085, 2247, 2247]}, {"bed": 1, "bath": 1, "price": [2144]}, {"bed": 1, "bath": 1, "price": [2223, 2177]}, {"bed": 1, "bath": 1, "price": [2205]}, {"bed": 1, "bath": 1, "price": [2237]}, {"bed": 2, "bath": 2, "price": [2982, 2982, 2982, 3017, 3162]}, {"bed": 2, "bath": 2, "price": [3297]}]

I want to get price min value where bed >0 and price >2000

I tried this

SELECT id,((jsonb_array_elements(p.bedBathPrice)->'bed'))  FROM 
properties p where p.id = 2

So can I do something below like bed >0 and price >2000 and then get min price

but what if I need to add p.bedbathprice in that value

    with the_original_table ( doc_data) as 
(
 values
 ( p.bedbathprice::jsonb)
),
normalized_data as
(
 select 
        (j ->> 'bath')::integer as bath, 
        (j ->> 'bed')::integer as bed, 
        p::numeric as price
 from the_original_table 
 cross join lateral jsonb_array_elements(doc_data) as j
 cross join lateral jsonb_array_elements(j -> 'price') as p
) 
select p.id ,(select min(nd_price) as min_price from (select (price) as nd_price from normalized_data nd where nd.bed>=2 and nd.price>2085)as nd) from properties p where p.parent_id  is not null and p.id=3 
and (exists(select 1 from normalized_data where bed>=2 and price>2085));

like this??? it says from clouse can't use p.bedbthprice in value

CodePudding user response：

with j as (select jsonb_array_elements('[
{"bed": 0, "bath": 1, "price": [1768]},
{"bed": 0, "bath": 1, "price": [1824, 1824, 1828, 1869, 1869]},
{"bed": 1, "bath": 1, "price": [2085, 2247, 2247]},
{"bed": 1, "bath": 1, "price": [2144]},
{"bed": 1, "bath": 1, "price": [2223, 2177]},
{"bed": 1, "bath": 1, "price": [2205]},
{"bed": 1, "bath": 1, "price": [2237]},
{"bed": 2, "bath": 2, "price": [2982, 2982, 2982, 3017, 3162]},
{"bed": 2, "bath": 2, "price": [3297]}
]'::jsonb) v) 
select j.v->>'bed' bed, (select min(value) from 
       jsonb_array_elements_text(j.v->'price')) minimum from j 
    where (j.v->>'bed')::integer >0;

output

 bed | minimum
----- ---------
 1   | 2085
 1   | 2144
 1   | 2177
 1   | 2205
 1   | 2237
 2   | 2982
 2   | 3297

CodePudding user response：

First 'normalize' the data. Then it's a trivial job and you are free to use all the power of SQL.

with the_original_table (id, doc_data) as 
(
 values
 (2, '[{"bed": 0, "bath": 1, "price": [1768]}, {"bed": 0, "bath": 1, "price": [1824, 1824, 1828, 1869, 1869]}, {"bed": 1, "bath": 1, "price": [2085, 2247, 2247]}, {"bed": 1, "bath": 1, "price": [2144]}, {"bed": 1, "bath": 1, "price": [2223, 2177]}, {"bed": 1, "bath": 1, "price": [2205]}, {"bed": 1, "bath": 1, "price": [2237]}, {"bed": 2, "bath": 2, "price": [2982, 2982, 2982, 3017, 3162]}, {"bed": 2, "bath": 2, "price": [3297]}]'::jsonb)
),
normalized_data as
(
 select id, 
        (j ->> 'bath')::integer as bath, 
        (j ->> 'bed')::integer as bed, 
        p::numeric as price
 from the_original_table 
 cross join lateral jsonb_array_elements(doc_data) as j
 cross join lateral jsonb_array_elements(j -> 'price') as p
) 
select *,  
       bed::text || '_' || bath::text || '_' || price::text as bed_bath_price
from normalized_data
where bed > 0 and price > 2000
order by price limit 1;

Unrelated but your data design is sub-optimal and hard to work with. A normalized one would be a much better choice.