CODEWITHSUNDEEP
sqlpostgresqljsonb
sscarduzio
sscarduzio

Reputation: 6188

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

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!

Upvotes: 2

Views: 254

Answers (1)

e4c5
e4c5

Reputation: 53774

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

Upvotes: 1

Related Questions