How can I search object values inside an array with Postgres jsonb?

Question

I'm using PostgreSQL 9.5 and the JSONB data type to store documents. My table something like this:

create table records (
  id serial,
  data jsonb
);

My documents contain an array of objects, for example:

{
  "some_field": "a value",
  "another_field": 123,
  entries: [
    {
      "name": "John Doe",
      "age": 42
    },
    {
      "name": "Johnny McMuffin",
      "age": 117
    }
  ]
}

The problem is that I would like to be able to filter on the name attribute in the entries array and I just can't figure it out. I want to be able to find rows in my table that partially match one of the names in the list of objects.

I've read a lot about indexes and expressions and stuff but I just can't seem to get it to work. Shouldn't this be possible?

score 9 · Accepted Answer · 2016-11-20 16:49:27Z

9

It's unclear to me what the expected result is, but something like this will work:

select r.id, e.*
from records r
  cross join lateral jsonb_array_elements(r.data -> 'entries') as e
where e ->> 'name' like '%Doe%';

In order to be able to access each array element to you need to "unnest" them (i.e. normalize the de-normalized document). Note that the above will return one row for each matching array element, not for each row in the table.

You can also move the check on the name to an exist sub-query if you need unique complete rows from the base table:

select r.*
from records r
where exists (select 1 
             from jsonb_array_elements(r.data -> 'entries') as e
             where e ->> 'name' like '%Doe%');

The difference between the two statements is, that the first query will only you show you the matching array elements. The second one will show you all array elements of a document if at least one of the matches.

edited Nov 20, 2016 at 16:49

answered Nov 20, 2016 at 16:37

user330315

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

2 Comments

fiskeben Over a year ago

This seems to work. I guess I can deal with the possible duplicates in some way.

user330315 Over a year ago

Those aren't duplicates. Those are multiple values from your array. But you can use an exists query to get only unique rows from the base table.

Collectives™ on Stack Overflow

How can I search object values inside an array with Postgres jsonb?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related