0

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.

0 Answers0