Updating in a loop for parent/child objects

SQL++
, ,
1

Hello everyone,
I am fairly new to couchbase and learning. I’ve got this task about parent/child relationship but not sure of how to go about it as im still not familiar with the full potential of N1QL. I’ll be using dummy values to simplify the structure, let us suppose we have a parent document and child document like below:

Parent:

{
“type”:“human”,
“id”:“John Doe”,
“familyTree”: “A1B2C3”,
“hasChildren”:null,
“gaveBirth”: true,
“myParentId”:null
}

Child:

{
“type”:“human”,
“id”:“John Doe Jr”,
“familyTree”: “A1B2C3”,
“hasChildren”:null,
“gaveBirth”: false,
“myParentId”:null
}

Each child can have one parent but each parent can have many children. So I use the myParentId field to point to parent id field. Also prerequisite: hasChildren will always be null at the start.
So basically what Id like to achieve is loop through all documents of type “human” and if gaveBirth is true, set hasChildren to true else false. If hasChildren it means document is a parent. That is pretty easy to achieve using UPDATE and WHERE clause but this is where the tricky part comes in that I can figure out how to proceed.

Id like to loop through all documents of type “human” and if gaveBirth is false (meaning child document) find a parent document whose familyTree value is the same as this child document and assign child.myParentId = parent.id

I’ve read up on join clauses and EVERY but can’t seem to put the puzzles together and im looking for some guidance of how to structure or how to continue.

2 
UPDATE default AS d
SET d.myParentId =  (FIRST  p.id FOR p IN  (SELECT d1.familyTree , d1.id  FROM default d1 WHERE d1.type = "human" AND d1.gaveBirth = true AND  d1.hasChildren = true) WHEN d.familyTree = p.familyTree END)
WHERE d.type = "human" AND d.gaveBirth = false AND  d.myParentId  IS NULL;