Let's say we have a Postgres table contacts
, each record having a bunch of labeled email addresses (pairs of labels and emails)-- one of which is the "primary".
This is stored like:
id
primary keyemail
textemail_label
textmetadata
jsonbemails
arrayemail
textlabel
text
For example, a record might look something like:
{
id: 1,
email: '[email protected]',
email_label: 'a',
metadata: {
emails: [
{
email: '[email protected]',
label: 'b'
},
{
email: '[email protected]',
label: 'c'
}
]
}
}
Given this storage pattern, we want to be able to find a record by any of its email addresses.
The naive query would look like:
SELECT id
FROM contacts
WHERE
email = '[email protected]' OR
metadata -> 'emails' @> '[{"email": "[email protected]"}]'
Is there any way to create an index that speeds up this operation significantly? It would need to update automatically in response to changes to the records, and ideally index across both the text column and the nested JSONB column.
The specific use case here would be able to do lookups by email address efficiently and quickly, without overhauling this structure or creating a new relational table.
I believe the solution involves using a GIN index and this question mentions jsonb_path_ops but I'm not sure how to wrangle all the pieces together.