如何过滤jsonb数组元素

Question

我有一个包含一个对象数组的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"]

- lalol

Answer 1

使用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) 

- klin

---

如果我想过滤多个关键字怎么办？ - Taufik Nurhidayat

@TaufikNurhidayat 在 where 子句中只是几个 OR，或者 elem->>'status' IN ['active', 'closed'] - New Alexandria

Answer 2

你可以使用简单的for循环来完成这个任务。

var obj = [{"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"}];

var tmpObj = [];

var i;
for (i = 0; i < obj.length; i++) { 
  if (obj[i]['status'] == 'active') {
    tmpObj.push(obj[i]['phone'])
  }
  console.log(tmpObj);
}

这种方法将循环遍历所有记录，并将“active”号码推送到一个临时数组中。这将输出["929908338", "928424554"]

可能有更有效的方法来完成这个任务，但这是我能想到的最简单的方法。

- Rajbir

---

根据问题中的标签，我怀疑 OP 是在寻找一个数据库查询。 - quiram