I have parent and child records that are identical in fields. However certain fields are only set at a parent level.
The parent field set to an empty string ("") signifies that the record is a parent. The other records have a value set which point to the parent hence these can be considered child records.
Now consider the following records:
{"_id": 1, parent: "", "pValue": ["a", "b", "c"], fieldA: 2},
{"_id": 2, parent: 1, "pValue": [], fieldA: 2},
{"_id": 3, parent: 1, "pValue": [], fieldA: 2},
{"_id": 4, parent: "", "pValue": ["d"], fieldA: 9},
{"_id": 5, parent: 4, "pValue": [], fieldA:2},
{"_id": 6, parent: 4,"pValue": [], fieldA: 9}
The above records contain two parents each having 2 associated child records. The query I'm trying to perform involves matching two given parameters. First a value on the pValue. Once I get all the parents with that specific pValue in their array. I then want to match that parent AND all it's associated child records to the fieldA value.
So if given pValue="d" and fieldA=9 I want the following records within the cursor:
{"_id": 4, parent: "", "pValue": ["d"], fieldA: 9}
{"_id": 6, parent: 4, "pValue": [],fieldA: 9}
Notes:
My Attempt:
cursor=self.pCollection.aggregate([
{ "$match": {"pValue":{"$in":[pCheck]}},
{ "$graphLookup" : {
"from": "pCollection",
"startWith": "$_id",
"connectFromField": "_id",
"connectToField": "parent",
"as" : "children"
}
}])
I then got stuck with all the children being associated with the parent as an array without knowing how to upwrap them.
You need to select the documents where that match your query criteria using $match and filter the "children" array in add an $addFields stage.
After $filtering, you can use the arrayElemAt to assign that the item to a field.
{ "$match": { "fieldA": 9, "children.fieldA": 9 } },
{ "$addFields": {
"children": {
"$arrayElemAt": [
{ "$filter": {
"input": "$children",
"as": "child",
"cond": { "$eq": [ "$$child.fieldA", 9 ] }
}},
0
]
}
}}
Your query will yield something like this:
{
"_id" : 4,
"parent" : "",
"pValue" : [ "d" ],
"fieldA" : 9,
"children" : { "_id" : 6, "parent" : 4, "pValue" : [ ], "fieldA" : 9 }
}
However, with a little faith, you can get your expected result.
db.collection.aggregate([
{ "$graphLookup": {
"from": "collection",
"startWith": "$_id",
"connectFromField": "_id",
"connectToField": "parent",
"as" : "children"
}},
{ "$match": { "fieldA": 9, "children.fieldA": 9 } },
{ "$addFields": {
"children": {
"$arrayElemAt": [
{ "$filter": {
"input": "$children",
"as": "child",
"cond": { "$eq": [ "$$child.fieldA", 9 ] }
}},
0
]
}
}},
{ "$project": {
"children": [
"$children",
{
"_id": "$_id",
"parent": "$parent",
"pValue": "$pValue",
"fieldA": "$fieldA"
}
]
}},
{ "$unwind": "$children" },
{ "$replaceRoot": { "newRoot": "$children" } }
])
which produces something like this:
{ "_id" : 6, "parent" : 4, "pValue" : [ ], "fieldA" : 9 }
{ "_id" : 4, "parent" : "", "pValue" : [ "d" ], "fieldA" : 9 }
Frankly I don't think this is something you should be doing in your application. The first option is something you can and should live with.
If you really need this, I suggest you create a view and query the view in your application.
By reversing the query and first matching on fieldA then doing a graphyLookup from parent--> _id (hence mapping the parent into the child) I was able to then associate the children to have their parent's fieldA.
This is the query I ended up with:
cursor=self.collection.aggregate([
{ "$match": {"fieldA":9}},
{ "$graphLookup" : {
"from": "collection",
"startWith": "$parent",
"connectFromField": "parent",
"connectToField": "_id",
"as" : "parents"
}
},
{ "$project" : {
"pValue": { "$cond":[ {"$ne":["$pValue",[]]},"$pValue",
{"$let":{"vars": {"obj": {"$arrayElemAt": ["$parents", 0]}},
"in": "$$obj.pValue"}}]}
}
},
{ "$match": {"pValue":{"$in":["d"]}}}
])