How can i access (query) Object in Array of another Object Array

Another option is use WITHIN.

SELECT  e.*
FROM default AS d USE KEYS "p2"
UNNEST (ARRAY v FOR v WITHIN d WHEN v.email_address IS NOT MISSING END) AS e;

Adding “type”: “email” in email objects and “type”:“people” in people object gives more flexibility.

SELECT  e.*
FROM default AS d USE KEYS "p2"
UNNEST (ARRAY v FOR v WITHIN d WHEN v.type = "email" END) AS e;

 SELECT  p.*, e.*
 FROM default AS d USE KEYS "p2"
 UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = "people" END) AS p
 UNNEST (ARRAY v FOR v WITHIN p WHEN v.type = "email" END) AS e;

Ok i made some changes to my doc as per your suggestion and the following query returns me the phone nbr’s for a given person in a lead doc

SELECT e.*
FROM default AS d USE KEYS "leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3"
UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = "person" END) AS p
UNNEST (ARRAY v FOR v WITHIN p WHEN v.type = "phone" END) AS e
where p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850"

the only question is now how do i get access to the prefixed id as the SDK requires to do subdoc operations. In my case the nbr is stored like this in doc

      {
        "1234": {
          "type": "phone",
          "id": 2,
          "country": "+1",
          "phone_number": 2135554444,
          "ext": 455,
          "nbrtype": "office",
          "sms": "no",
          "default": "yes"
        }}

but the query returns only

         {
          "type": "phone",
          "id": 2,
          "country": "+1",
          "phone_number": 2135554444,
          "ext": 455,
          "nbrtype": "office",
          "sms": "no",
          "default": "yes"
        }

so how do i get access to the 1234 in my query either to get value or as a search criteria ? i dont know the value
which is a key field but changes for every record. If there is no way to access it i will have to store it also in the actual object. I am planning to use N1QL to query the bucket and return info to user and then use the subdoc
function in SDK to update contact objects in doc. But for that i need the 1234 as it is part of the path. Actually i also would need the key for the person as the full path would be something like this

bucket.mutateIn('leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3').remove('person[{personkey}].phone[{phonekey}]')

I thought you are going to use id as field name. If not it is difficult to get with WITHIN you need to use previous OBJECT_PAIRS() query.

Also following is more efficient

UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = “person” AND v1.id = “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850” END) AS p

Ok i tried

SELECT e.*FROM default AS d USE KEYS "leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3"
UNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = "person" WHEN v1.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" END) AS p
UNNEST (ARRAY v FOR v WITHIN p WHEN v.type = "phone" END) AS e

which does not work as it throws the following error

  {
  "code": 3000,
  "msg": "syntax error - at WHEN",
  "query_from_user": "SELECT e.*\r\nFROM default AS d USE KEYS \"leads::f73cfadf-8b72-4a95-b3e3-ddda745600e3\"\r\nUNNEST (ARRAY v1 FOR v1 WITHIN d WHEN v1.type = \"person\" WHEN v1.id = \"cc54bfc9-21de-4729-8ebb-b5ffcd6b4850\" END) AS p\r\nUNNEST (ARRAY v FOR v WITHIN p WHEN v.type = \"phone\" END) AS e"
  }

So to make all this work efficiently i will store the key of the object also inside the object as well as tag them with a type to make filtering easyer

There are two WHEN replace second one with AND

Thanks i did quick copy paste and didn’t see that. But yes it works as hoped…