PostgreSQL: Search by json property in JSONB column that is populated as json array

Question

So I have this:

CREATE EXTENSION "uuid-ossp"; -- not related, just for the uuid_generate_v4

CREATE TABLE events (
  id UUID PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  speakers JSONB NOT NULL DEFAULT '[]'::jsonb
);

INSERT INTO events (id, speakers) values (uuid_generate_v4(), '[
  {
    "id": "de3ae2c7-19f6-4c69-81ae-467034c06101",
    "email": ""
  },
  {
    "id": "c8cf8fe8-a6b7-4cbc-b2c7-729c6108ff5f",
    "email": "[email protected]"
  }
]'::JSONB)

I need to get the list of event.id where '[email protected]' appears in the "email" field at least once in the speakers JSONB column.

I tried:

select id from events where events.speakers->'email' ? '"[email protected]"';
<no results>

And many other snippets that never worked. I don't think this is such an unusual usage pattern!

Answer 1

You have to expand the array first with jsonb_array_elements

SELECT * FROM (
   select id, jsonb_array_elements(speakers) as event from events
) as a where event->'email' ? '[email protected]'