How can i only return the Doc in array which matches where statement?

SQL++
1

I have docs for contacts which have an array of docs called emails. The blow gets the the farm who has an email which matches my clause. But it does not only return the email element with the address match but all emails for the farm that has a doc with the matching email.
So how can i limit the return to only the email doc in array which also matches the e.address ?

select c.emails from Contacts c
        where c._type= "farm" AND ANY e IN c.emails SATISFIES e.address = 'user@bellsouth.net' END;
2 
select ARRAY e FOR e IN c.emails  WHEN e.address = 'user@bellsouth.net' END AS emails 
from Contacts c
        where c._type= "farm" AND ANY e IN c.emails SATISFIES e.address = 'user@bellsouth.net' END;
3

Ok that now gets me an array where object match but it looks like this…

[{
    "email": [{
        "address": "user@bellsouth.net",
        "bounce": true,
        "id": "6c6f4b14-3c8b-4f02-b7a4-644cc11daab3",
        "name": "user@bellsouth.net",
        "source": "Intelius",
        "type": "default"
      } ]
  }]

how can i get this below output where first object is a object not an array

{
    "email": [{
        "address": "user@bellsouth.net",
        "bounce": true,
        "id": "6c6f4b14-3c8b-4f02-b7a4-644cc11daab3",
        "name": "user@bellsouth.net",
        "source": "Intelius",
        "type": "default"
      } ]
  }

also is it possible to get only the bounce and id from the array and return in format like this

[{
        "bounce": true,
        "id": "6c6f4b14-3c8b-4f02-b7a4-644cc11daab3",
}]
4 
 select  FIRST { e.id, e.bounce} FOR e IN c.emails  WHEN e.address = 'user@bellsouth.net' END AS emails 
 from Contacts c
            where c._type= "farm" AND ANY e IN c.emails SATISFIES e.address = 'user@bellsouth.net' END
;
5

Ok i used Array vs Single and i get the following, how can i get rid of the outer most array element as well as the object and Emails

[
{
"email": [
  {
    "bounce": true,
    "id": "6c6f4b14-3c8b-4f02-b7a4-644cc11daab3"
  }
]
}]
6

Post exact query the query earlier post doesn’t produce this results.

select RAW  FIRST { e.id, e.bounce} FOR e IN c.emails  WHEN e.address = 'user@bellsouth.net' END 
from Contacts c
where c._type= "farm" AND ANY e IN c.emails SATISFIES e.address = 'user@bellsouth.net' END;

select  e.id, e.bounce
from Contacts c
UNNEST c.emails AS e
where c._type= "farm" AND e.address = 'user@bellsouth.net';
7

Here is the query i used

select raw array { e.id, e.bounce}FOR e IN c.emails  WHEN e.address = myEmail END AS email
from Contacts c LET myEmail = 'user@bellsouth.net'
    where c._type= "farm" AND ANY e IN c.emails SATISFIES e.address = myEmail END;
8

You can’t remove outer array that is from results. As you need all matching entries, you can flatten as follows.

select  e.id, e.bounce
from Contacts c
UNNEST c.emails AS e
where c._type= "farm" AND e.address = 'user@bellsouth.net';
9

Yes , with the INNEST i can get it done but i had the issue with the e In selection. So let me ask this, is there a rule of thumb when to use what ? Also if so does either have a huge speed benefit ?

10

UNNEST is self JOIN between original document with each array element, will expand documents and can be slow. If you need to select the array elements you must use unnest. Or reconstruct and you get ARRAY.

select  e.id, e.bounce
from Contacts c
UNNEST c.emails AS e
where c._type= "farm" AND e.address = 'user@bellsouth.net';