本文介绍了如何筛选jsonb数组元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有jsonb列的表,其中包含一个对象数组:
create table my_table( id int primary key, phones jsonb);对象由电话号码和状态组成。
insert into my_table values (1, '[ { "phone": "929908338", "status": "active" }, { "phone": "927007729", "status": "inactive" }, { "phone": "927220419", "status": "inactive" }, { "phone": "928200028", "status": "inactive" }, { "phone": "927183322", "status": "inactive" }, { "phone": "928424554", "status": "active" }, { "phone": "927779383", "status": "inactive" } ]');我希望在一行中获取所有处于活动状态的电话。预期输出:
["929908338","928424554"] 推荐答案使用jsonb_array_elements()取消嵌套jsonb数组:
select id, elem from my_table cross join jsonb_array_elements(phones) as elem id | elem ----+---------------------------------------------- 1 | {"phone": "929908338", "status": "active"} 1 | {"phone": "927007729", "status": "inactive"} 1 | {"phone": "927220419", "status": "inactive"} 1 | {"phone": "928200028", "status": "inactive"} 1 | {"phone": "927183322", "status": "inactive"} 1 | {"phone": "928424554", "status": "active"} 1 | {"phone": "927779383", "status": "inactive"} (7 rows)添加条件以获取您要查找的元素:
select id, elem from my_table cross join jsonb_array_elements(phones) as elem where elem->>'status' = 'active' id | elem ----+-------------------------------------------- 1 | {"phone": "929908338", "status": "active"} 1 | {"phone": "928424554", "status": "active"} (2 rows)使用聚合函数jsonb_agg()获取单行结果:
select id, jsonb_agg(elem->'phone') as active_phones from my_table cross join jsonb_array_elements(phones) as elem where elem->>'status' = 'active' group by id id | active_phones ----+---------------------------- 1 | ["929908338", "928424554"] (1 row)更多推荐
如何筛选jsonb数组元素
发布评论