CODEWITHSUNDEEP
arrayspostgresqljsonb
fiskeben
fiskeben

Reputation: 3467

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

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?

Upvotes: 4

Views: 8712

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 9

Related Questions