0

A simplified example of the data structure is 

{ _id: ObjectId, parentId: ObjectId }

where a parent will have parentId blank, and all the children will have parentId point to the top level record that is in the same collection.

I have an Array of ID’s that can either be a parent or child ID and I need to retrieve all the parent records.

So if the ID is for a parent, cool all set.

If the given ID points to a record that has a parentId, retrieve the record with the _id === parentId.

Lastly, I can't have duplicates so if I have a child ID that points to a parent record that I already have (because I was given the ID explicitly), just take the unique parent record.

so I could have a query like: 

query: { _id: {$in: [2,3,4]}}

with data like 

_id  |   parentId 
1    |   undefined
2    |   1
3    |   undefined
4    |   3

which should return the records for 1, 3 because 2 points to 1, and although I have 3 and 4, the top level is 3.

Adam James
  • 3,833
  • 6
  • 28
  • 47
  • Is there any reason you can't simply search for all the records with an undefined parentId? Wouldn't such a query return all the parent records? – crash Oct 15 '19 at 20:56

1 Answers1

0

If the given ID points to a record that has a parentId, retrieve the record with the _id === parentId.

These records can be retrieved using the $lookup stage of the aggregation pipeline:

$lookup:
     {
       from: <collection name>,
       localField: parentId,
       foreignField: _id,
       as: parentObj
     }

I can't have duplicates so if I have a child ID that points to a parent record that I already have (because I was given the ID explicitly), just take the unique parent record.

The aggregation pipeline doesn't really mesh nicely with distinct. Depending on your implementation, you may be able to use the $group stage as a workaround, as shown by this question.

crash
  • 300
  • 1
  • 7