I have a Postgres Table "Organization" containing "email_details" column of JSONB data. This column will hold data in below format.
{
"emails":
[
{
"recipients": ["abc_email@dummy.com", "xyz_email@dummy.com"],
"email_body": "test1",
},
{
"recipients": ["abc_email@dummy.com"],
"email_body": "test2",
},
{
//"recipients": [""],
"email_body": "test3",
},
],
"other_data": { ... }
}
Scenario 1. I would like to get the list of emails for a particular user using Select query.
For Example,
a). If I send recipient id as : "abc_email@dummy.com" in WHERE Clause, it should return me emails array containing first two objects only.
b). If I send recipient id as : "xyz_email@dummy.com" in WHERE Clause, it should return me emails array containing first object only.
Scenario 2. I would like to get the entire email_data column if the email_id passed in WHERE clause is present for any one of the recipients in the emails array.
For Example,
a). If I send recipient id as : "xyz_email@dummy.com" in WHERE Clause, it should return me entire email_data as it is.
Kindly suggest appropriate Postgres SELECT query.