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"]

klin · Accepted Answer · 2019-01-02 13:11:51Z

27

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)

answered Jan 2, 2019 at 13:11

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Taufik Nurhidayat Over a year ago

What if I want filter more than one key?

New Alexandria Over a year ago

@TaufikNurhidayat just a few OR in the where clause, or elem->>'status' IN ['active', 'closed']

Rajbir · Accepted Answer · 2019-01-02 13:26:59Z

-11

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

answered Jan 2, 2019 at 13:26

Rajbir

4212 gold badges4 silver badges13 bronze badges

1 Comment

quiram Over a year ago

Based on the labels in the question, I suspect OP is after a DB query.

Collectives™ on Stack Overflow

How to filter jsonb array elements

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related