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

In my case i have a Doc which is a Lead, this lead can have more then one Person and each person can have more then 1 phone number or email. I was able to get the value or the element of a Phone number based on given if of user and phone nbr like this

SELECT p.id,e from default as d
UNNEST d.person p
UNNEST p.email AS e
where p.id = “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850” and e.id = “3416e4cc-89df-4537-b86f-a417614ebe12”

which returns me the data like this which is fine

[

{
“e”: {
“default”: “yes”,
“email_address”: "tom1@email.com",
“id”: “3416e4cc-89df-4537-b86f-a417614ebe12”
},
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850”
}
]

Now my question is how do i for example update the value of default to '“no” or how can i delete the default elment all together ? Also is there a way to to insert a new doc into the array via N1SQL ?

UPDATE default AS d
SET e.default = "no"
    FOR e IN p.email
    FOR p IN d.person
   
    WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" AND e.id = "3416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" AND
        (ANY e IN p.email SATISFIES e.id = "3416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes"  END) END;

UPDATE default AS d
UNSET e.default
     FOR e IN p.email
    FOR p IN d.person
    WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" AND e.id = "3416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" AND
        (ANY e IN p.email SATISFIES e.id = "3416e4cc-89df-4537-b86f-a417614ebe12" AND e.default IS NOT MISSING  END )END;

Append if not present.

UPDATE default AS d
SET p.email = ARRAY_APPEND(p.email, { "default": "yes", "email_address": "tom1@email.com", "id": "4416e4cc-89df-4537-b86f-a417614ebe12"
})
    FOR p IN d.person WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850" AND
        NOT (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END ) END;

You can add any condition to WHERE clause. The above conditions avoids unnecessary mutations.

Check last example https://docs.couchbase.com/server/5.5/n1ql/n1ql-language-reference/update.html

Thanks that makes it a lot more clear as I don’t care about index in array and all my objects have a guid. Most samples I found used a single value in array and used index to update. The way I understand your code even so my person array is in a lead doc which has a docid I do not have to filter based on docid as couchbase can access the person element directly via the id ? Would there be a performance gain to ad a selection for the doc or does it not matter if i have an index on the person.id ?

If you know docid you can use USE KEYS and no need index or additional filter

I am bit confused by your last comment , of course do I have the docid because my app will have a list of person displayed based on the lead which they opened, so my doc id is lead::guid. So should I add the docid filter to the where clause. The way I understand your comment, if I do that I would not need additional index as the docid is an existing index and since it can find the person Id on doc easily without index. If I don’t have docid I should create index to avoid having couchbase read all docs to find the person Id.
If I am correct does it matter where I place the meta().id = lead::xxxxxxxx so it will use the index or is couchbase smart enough ?

If you know docids and want to update only that documents you can use following syntax. No index or filter on META().id is needed.

UPDATE default AS d USE KEYS ["lead::xxxxxxxx", "lead::yyyyy", .....] ..................

Ok i tried your code and the Append works as expected, the folowing which was supposed to set the default to no for a given email in array does not.

here is the code i ran for N1QL

UPDATE default AS d
SET e.default = "no"
FOR p IN d.person
FOR e IN p.email
WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END ) AND e.default = "yes" END;

here is the sample doc i tried it against
{
“person”: [
{
“dob”: “07/12/1978”,
“email”: [
{
“default”: “yes”,
“email_address”: "mymail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe12”
}
],
“first_name”: “Frank”,
“gender”: “m”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850”,
“last_name”: “Smith”,
“middle_name”: “J”,
“suffix”: “II”,
“title”: “Dr.”
},
{
“dob”: “07/12/1978”,
“email”: [
{
“default”: “yes”,
“email_address”: "noemail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe12”
}
],
“first_name”: “Susi”,
“gender”: “f”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4851”,
“last_name”: “Smith”,
“middle_name”: “M”,
“suffix”: “I”,
“title”: “”
}
],
“type”: “lead”
}

AND e.default =“yes” in wrong place (out of scope). It needs inside END

UPDATE default AS d
SET e.default = "no"
FOR e IN p.email
FOR p IN d.person
WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END;

i tried that but it still does not update the default key in the email. I even went this far as changing the where clause to

WHERE ANY p IN d.person SATISFIES p.id = “cc54bfc9-21de-4729-8ebb-b5ffcd6b4851” END;

in hope that would update all e.default for the given user to no but that did not work either. What are we missing here ? Also to minimize scan time how can i also add the doc id as a condition ?

FOR clause in SET needs to be intercahnged

UPDATE default AS d 
SET e.default = "no"
FOR e IN p.email
FOR p IN d.person
WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END;
1 Like

Thanks this works, final question how would I add to this query if I want to also filter by docid ?

If you already know docid replace docid in USE KEYS

UPDATE default AS d  USE KEYS ["docid"]
SET e.default = "no"
        FOR e IN p.email
                FOR p IN d.person
                     WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
    (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END;

If you don’t know it and want to search and update all qualified documents add condition to WHERE clause. example docids starts with “person::”

 UPDATE default AS d  
  SET e.default = "no"
            FOR e IN p.email
                    FOR p IN d.person
                         WHEN p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" 
                                      AND e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" END
    WHERE ANY p IN d.person SATISFIES p.id = "cc54bfc9-21de-4729-8ebb-b5ffcd6b4851" AND
        (ANY e IN p.email SATISFIES e.id = "4416e4cc-89df-4537-b86f-a417614ebe12" AND e.default = "yes" END) END
         META(d).id LIKE "person::%";

when i think i finally figured it out i find out that my json doc will not work because of limitations in the SDK. The SDK does not allow access based on a key in path but needs to get the full path. That said i will have to change my
doc to store emails to something like this where the id is the element name
and i can create a path like email.4416e4cc-89df-4537-b86f-a417614ebe12.default.

how would this impact my N1QL query if i wanted to list all email_address in query since ?
I am just trying to see if i can find a midle ground which allows me to use both SDK and N1QL
based on each limitations

This s what the doc would have to look like to meet the SDK subdoc requirments
{
“person”: [
{
“cc54bfc9-21de-4729-8ebb-b5ffcd6b4850”: {
“dob”: “07/12/1978”,
“email”: [
{
“4416e4cc-89df-4537-b86f-a417614ebe12”: {
“default”: “yes”,
“email_address”: "mymail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe12”
}
}
],
“first_name”: “Frank”,
“gender”: “m”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4850”,
“last_name”: “Smith”,
“middle_name”: “J”,
“suffix”: “II”,
“title”: “Dr.”
}
},
{
“cc54bfc9-21de-4729-8ebb-b5ffcd6b4852”: {
“dob”: “07/12/1978”,
“email”: [
{
“4416e4cc-89df-4537-b86f-a417614ebe13”: {
“default”: “soso”,
“email_address”: "noemail@email.com",
“id”: “4416e4cc-89df-4537-b86f-a417614ebe13”
}
}
],
“first_name”: “Sam”,
“gender”: “f”,
“id”: “cc54bfc9-21de-4729-8ebb-b5ffcd6b4852”,
“last_name”: “Smith”,
“middle_name”: “M”,
“suffix”: “I”,
“title”: “”
}
}
],
“type”: “lead”
}

INSERT INTO default VALUES ("p2",{ "person": [ { "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850": { "dob": "07/12/1978", "email": [ { "4416e4cc-89df-4537-b86f-a417614ebe12": { "default": "yes", "email_address": "mymail@email.com", "id": "4416e4cc-89df-4537-b86f-a417614ebe12" } } ], "first_name": "Frank", "gender": "m", "id": "cc54bfc9-21de-4729-8ebb-b5ffcd6b4850", "last_name": "Smith", "middle_name": "J", "suffix": "II", "title": "Dr." } }, { "cc54bfc9-21de-4729-8ebb-b5ffcd6b4852": { "dob": "07/12/1978", "email": [ { "4416e4cc-89df-4537-b86f-a417614ebe13": { "default": "soso", "email_address": "noemail@email.com", "id": "4416e4cc-89df-4537-b86f-a417614ebe13" } } ], "first_name": "Sam", "gender": "f", "id": "cc54bfc9-21de-4729-8ebb-b5ffcd6b4852", "last_name": "Smith", "middle_name": "M", "suffix": "I", "title": "" } } ], "type": "lead" });

UPDATE default AS d  USE KEYS ["p2"]
SET e.["4416e4cc-89df-4537-b86f-a417614ebe12"].default = "no"
        FOR e IN  p.["cc54bfc9-21de-4729-8ebb-b5ffcd6b4850"].email
                FOR p IN d.person END
WHERE ANY p IN d.person SATISFIES (ANY e IN p.["cc54bfc9-21de-4729-8ebb-b5ffcd6b4850"].email SATISFIES e.["4416e4cc-89df-4537-b86f-a417614ebe12"].default = "yes" END) END;

thanks for all the help but this is getting worse by the minute. Maybe the Doc based approach is not ready for what i am trying to do. A simple query in SQL like
update email_tbl set default = ‘no’ where person_id = xxx and email_id = yyy turns into a piece of art. Before this design change in Doc due to sdk limitations i was able to UNNEST person and Email and was able to get a list of email that does no longer work either , how would one go about that as the person and email are still arrays but now have a key value as key

You can also do this way.

   UPDATE default AS d  USE KEYS ["p2"]
    SET e.`4416e4cc-89df-4537-b86f-a417614ebe12`.default = "no"
            FOR e IN  p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4850`.email
                    FOR p IN d.person END
    WHERE ANY p IN d.person SATISFIES (ANY e IN p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4850`.email
                                       SATISFIES e.`4416e4cc-89df-4537-b86f-a417614ebe12`.default = "yes" END)
    END;

OR

Always update document even though no change.

 UPDATE default AS d  USE KEYS ["p2"]
        SET e.`4416e4cc-89df-4537-b86f-a417614ebe12`.default = "no"
                FOR e IN  p.`cc54bfc9-21de-4729-8ebb-b5ffcd6b4850`.email
                        FOR p IN d.person END;

Thanks but I guess we had a misunderstanding , i get the update part as I will have the key value for p and e abt that point but my question was more how would I get all key values and associated email address as at that point I do not know the person Id or it’s associated email id. That’s why I mentioned the basic unnest no longer works.

posted over here Subdocument operations

SELECT  pp.name AS pid , ep.name AS eid, ep.val.*
    FROM default AS d
    UNNEST d.person AS p
     UNNEST OBJECT_PAIRS(p) AS pp
    UNNEST pp.val.email AS e
    UNNEST  OBJECT_PAIRS(e) AS ep;

Thanks I saw it. Is there any plans to enhance the kv sdk to allow to update a array based on a value in it ? And if I retrieve a array via subdoc operation in sdk is there a way to get the actual array index for the returned object ?
Based on what i read the sdk kv aproach is way faster as it does not use index but N1QL is so much more flexible ?

If you know document key SDK operation is much faster. If you need flexible and don’t know document key N1QL is better. I don’t know about roadmap for support.