如何筛选jsonb数组元素 [英] How to filter jsonb array elements

问题描述

我有一个带有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数组元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！