Relate children to parent using Mongo aggregate query

Question

I have a single collection named assets that contains documents in 2+ formats, ParentObject and ChildObject. I am currently associating ParentObject to ChildObject with two queries. Can this be done with an aggregate query?

ParentObject

{
    "_id" : {
        "oid" : "ParentFooABC",
        "brand" : "acme"
    },
    "type": "com.ParentClass",
    "title": "Title1234",

    "availableDate": Date,
    "expirationDate": Date
}

ChildObject

{
    "_id" : {
        "oid" : "ChildFoo",
        "brand" : "acme"
    },
    "type": "com.ChildClass",
    "parentObject": "ParentFooABC",
    "title": "Title1234",
    "modelNumber": "8HE56",
    "modelLine": "Metro",
    "availableDate": Date,
    "expirationDate": Date,
    "IDRequired": true
}

Currently I filter data like this

val parent = db.asset.find(MongoDBObject("_id.brand": MongoDBObject($eq: "acme")),MongoDBObject("type":"com.ParentClass"))
val children = db.asset.find(MongoDBObject("_id.brand": MongoDBObject($eq: "acme")),MongoDBObject("type":"com.ChildClass"), MongoDBObject("parentObject": "${parent._id.oid}"))
if(childs.nonEmpty) {
  //I have confirmed this parent has a child associated and should be returned
  val childModelNumbers = childs.map(child -> child.modelNumber)
  val response = ResponseObject(parent, childModelNumbers)
}

Can I do this in an aggregate query?

Updated:

Mongo Version: db version v2.6.11

Language: Scala

Driver: Casbah 2.8.1

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Technically yes, however what you're doing now is standard practice with mongodb. If you need to join collections of data frequently you probably should use an RDBMS. However if you occasionally need to aggregate data from 2 separate collection then there is $lookup. In general you'll find yourself populating data into your document from another document pretty frequently, but that's typically how a document database works. Using $lookup when you're really looking for an equivalent to a SQL JOIN isn't something I would recommend. It's not really the intended purpose, and you'd be better off doing exactly what you're doing now or moving to an RDBMS.

Addition:

Since the data is in the same collection you can use an aggregation to $group that data together. You just have to group them based on _id.brand.

A quick example(in mongodb shell):

db.asset.aggregate([
{  //start with a match to narrow down
   $match: {"_id.brand": "acme"}
},
{ //group by `_id.brand`
   $group: {
      _id: "$_id.brand",
      "parentObject": {$first: "$parentObject"},
      title: {$first: "$title"},
      modelNumer: {$addToSet: "$modelNumer"}, //accumulate child model # as array
      modelLine: {$addToSet: "$modelLine"}, //accumulate child model line as array
      availableDate: {$first: "$availableDate"},
      expirationDate: {$first: "$expirationDate"}
   }
}
]);

This should return documents where all the children's properties have been grouped into the parent document as an array(modelNumber,modelLine). It's probably better to do what you're doing above, it might even be better yet if you put the children into their own collection instead of keeping track of their type with a type field in the document. This way you know that the documents in each collection represent a given data structure. However, if you were to do that you would not be able to perform the aggregation in the example.

Thanks for the quick response! I made a mistake by not originally including my Mongo version number and $lookup is not available to me [Mongo 2.6 doc](https://docs.mongodb.org/v2.6/reference/operator/aggregation/). Although it looks like I am performing a join, both the ParentObject and ChildObject are actually both in the same collection. I was hoping to get all the documents with $match and then compare them within the same aggregation pipeline — Kevin Harper, May 02 '16 at 22:11
@KevinHarper Yeah that was a pretty recent addition. Typically you'll have the parent and the child in different collections, store an id as a reference in the children or parent document and then querying for the children based on the parent's id or query based on an array of children id's stored in the parent. I totally missed the point that you had them both in the same collection. However you could probably `$group` by the `_id.brand` and grab whatever data you need from each document with your current approach. — tsturzl, May 03 '16 at 06:47
@KevinHarper You should update your question and I'll update my answer to match it. What language are you using? It would probably be helpful to tag it on your question. — tsturzl, May 03 '16 at 06:47
Excellent suggestion. I've updated my question with Mongo version, language (Scala) and driver (Casbah) — Kevin Harper, May 04 '16 at 02:50
@KevinHarper I don't have experience with casbah, however I can provide some examples as mongodb queries, and not formated with the casbah DSL. Would that be acceptable? — tsturzl, May 06 '16 at 18:54

Relate children to parent using Mongo aggregate query

1 Answers1

Addition: