如何筛选jsonb数组元素

本文介绍了如何筛选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)