How can i search different doc types and path for same type of object key

I have 2 type of documents contacts and farms, contacts have a an array of emails directly under the root of doc, my farms have the array under the Record key. Is there a way to search both doc types if there is any email object where the address is ‘demo@gmail.com’ I can do it in 2 querys but i am hoing to find a way to do it in 1.

Here is what one of the object looks like


    "emails": [
      {
        "address": "demo@gmail.com",
        "bounce": false,
        "id": "516df0b9-d4ec-4699-970d-22a122c067b2",
        "type": "Personal"
      }
    ]

The Query i came up with is something like this…

SELECT META(c).id ,
       Record.emails
FROM Contacts c
WHERE (_type ='contact'
        AND ANY e IN c.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END )
    OR (_type ='farm'
        AND ANY e IN c.Record.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END)

It somewhat works but the issue is that it does not return the email object in both cases since 1 is emails the other Record.emails. > I get the meta(id) of the object fine. Is there a way to make this work ?

SELECT META(c).id , emails
FROM Contacts c
LET emails = CASE WHEN c._type ="contact" THEN c.emails ELSE c.Record.emails END
WHERE (c._type = "contact" AND ANY e IN c.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END )
    OR (c._type = "farm" AND ANY e IN c.Record.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END)

OR

SELECT META(c).id , c.emails
FROM Contacts c
WHERE c._type = "contact" AND ANY e IN c.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END
UNION ALL
SELECT META(c).id , c.Record.emails
FROM Contacts c
WHERE c._type = "farm" AND ANY e IN c.Record.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END

If present either c.emails or c.Record.emails try this

SELECT META(c).id 
IFMISSING(c.emails, c.Record.emails) AS emails
WHERE (c._type = "contact" AND ANY e IN c.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END )
    OR (c._type = "farm" AND ANY e IN c.Record.emails SATISFIES LOWER(e.address) LIKE LOWER("demo@gmail.com") END)