如何获得jsonb阵列的最小值？
如何获得jsonb阵列的最小值？
提问于 2021-11-29 08:24:31
我有这个
[{"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]}]我想得到床>0和价格>2000的最低价格。
我试过这个
SELECT id,((jsonb_array_elements(p.bedBathPrice)->'bed')) FROM
properties p where p.id = 2 所以我可以在下面做一些事情，比如床>0和价格>2000，然后得到最低价格。
但是如果我需要在这个值中添加p.bedbathprice呢？
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));像这样？上面说克劳斯不能用p.bedbthprice的值
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-11-29 10:26:28
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;输出
bed | minimum
-----+---------
1 | 2085
1 | 2144
1 | 2177
1 | 2205
1 | 2237
2 | 2982
2 | 3297页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70151646
复制相关文章
相似问题
腾讯云开发者
