How to do an aggregation and lookup on double nested array in MongoDB?

user_534776 0
Viewed 152

I have three collections named issue, category and article, they are defined as follows:

// issue
{
      "_id": "612775fbd237d9769a9fc3e4",
      "title": "Weekly Issue1",
      "data": [
        {
          "categoryId": ObjectId("61272e7dd237d9769a9fc3d9"),
          "articles": [ ObjectId("61272f29d237d9769a9fc3da"), ...]
        },
        ...
      ]
    }
  ]
}

// category
{
   "_id"  : "61272e7dd237d9769a9fc3d9",
   "name" : "News"
}

// article
{
   "_id"   : "61272f29d237d9769a9fc3da",
   "title" : "Some News",
   "url"   : "https://www.google.com"
}

I would like to do an aggregation on issue and hope to get JSON results like this:

{
      "id": "612775fbd237d9769a9fc3e4",
      "tittle": "Weekly Issue1",
      "data": [
        {
          "categoryId": "61272e7dd237d9769a9fc3d9",
          "categoryName": "News"
          "articles": [ {id: "61272f29d237d9769a9fc3da", title:"Some News", url:"https://www.google.com"}, ...]
        },
        ...
      ]
    }
  ]
}

My question is how to write the aggregation script? I am just a newbie to MongoDB, I have no idea about it now.

db.issue.aggregate([])

Thanks :-)

aggregation-framework mongodb
3 Answers
  • $unwind deconstruct data array
  • $lookup with article collection
  • $lookup with category collection
  • $addFields to edit categoryName field, $arrayElemAt to get first element from result of category
  • $group by _id and reconstruct the data array and return required fields by $first operator
db.issue.aggregate([
  { $unwind: "$data" },
  {
    $lookup: {
      from: "article", // replace your original collection name
      localField: "data.articles",
      foreignField: "_id",
      as: "data.articles"
    }
  },
  {
    $lookup: {
      from: "category", // replace your original collection name
      localField: "data.categoryId",
      foreignField: "_id",
      as: "data.categoryName"
    }
  },
  {
    $addFields: {
      "data.categoryName": { $arrayElemAt: ["$data.categoryName.name", 0] }
    }
  },
  {
    $group: {
      _id: "$_id",
      title: { $first: "$title" },
      data: { $push: "$data" }
    }
  }
])

Playground

user_89871280

use this aggregate I think you could use $project instead of $addField but in test, I got some issue, and use addFields

[
      {
        '$lookup': {
          'from': 'category', 
          'localField': 'data.0.categoryId', 
          'foreignField': '_id', 
          'as': 'dataCategory'
        }
      }, {
        '$lookup': {
          'from': 'article', 
          'localField': 'data.0.articles', 
          'foreignField': '_id', 
          'as': 'dataArticles'
        }
      }, {
        '$unwind': {
          'path': '$dataCategory'
        }
      }, {
        '$addFields': {
          'data': [
            {
              'categoryId': '$dataCategory._id', 
              'categoryName': '$dataCategory.name', 
              'articles': '$dataArticles'
            }
          ]
        }
      }, {
        '$project': {
          'data': 1, 
          'tittle': 1
        }
      }
    ]
user_126385120

Because you have a nested array, you can do also a nested $lookup

  • with the first $lookup we add the category information
  • with the second nested $lookup we add the articles info
    (the middle $addFields is to keep from the joined-category only its own articles)

Test code here

db.issue.aggregate([
  {
    "$lookup": {
      "from": "category",
      "let": {"data": "$data"},
      "pipeline": [
        {"$match": {"$expr": {"$in": ["$_id","$$data.categoryId"]}}},
        {
          "$addFields": {
            "articles": {
              "$arrayElemAt": [
                {
                  "$filter": {
                    "input": "$$data",
                    "as": "d",
                    "cond": {"$eq": ["$$d.categoryId","$_id"]
                    }
                  }
                },0]}}
        },
        {
          "$lookup": {
            "from": "article",
            "localField": "articles.articles",
            "foreignField": "_id",
            "as": "articles"
          }
        }
      ],
      "as": "data"
    }
  }
])
user_48826920
Related