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”
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.
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 ?
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 ?
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;
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 ?
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;
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”
}
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
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.
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.