How to filter jsonb array elements

Question

I have a table with a jsonb column which contains an array of objects:

create table my_table(
    id int primary key, 
    phones jsonb);

The objects consist of phone numbers and statuses.

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"
    }
]');

I want to get all phones with active status in a single row. Expected output:

["929908338","928424554"]

score 21 · Accepted Answer · answered Jan 02 '19 at 13:11

Use jsonb_array_elements() to unnest the jsonb array:

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)

Add a condition to get elements you are looking for:

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)

Use the aggregate function jsonb_agg() to get the result in a single row:

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)

@TaufikNurhidayat just a few `OR` in the where clause, or `elem->>'status' IN ['active', 'closed']` — New Alexandria, May 09 '23 at 18:53

score -11 · Answer 2 · answered Jan 02 '19 at 13:26

You can use a simple for loop to get this done

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);
}

This method will loop through all the records and push the 'active' numbers to a tmp array. This will output ["929908338", "928424554"]

There may be a more effective way of doing this but it is the simplest way I could think of

Based on the labels in the question, I suspect OP is after a DB query. — quiram, Oct 19 '20 at 15:08

How to filter jsonb array elements

2 Answers2

Linked