CODEWITHSUNDEEP
mongodb
Ardian
Ardian

Reputation: 151

MongoDB inner join with specific condition from both collections

I have got two collections, chapters and courses with one-to-one association,

db={
  "chapters": [
    {
      "_id": 10,
      "course_id": 1,
      "author": "John"
    },
    {
      "_id": 20,
      "course_id": 2,
      "author": "John"
    },
    {
      "_id": 30,
      "course_id": 3,
      "author": "Timmy"
    },
    {
      "_id": 40,
      "course_id": 4,
      "author": "John"
    },
    
  ],
  "courses": [
    {
      "_id": 1,
      "published": true,
      "name": "course 1"
    },
    {
      "_id": 2,
      "published": false,
      "name": "course 2"
    },
    {
      "_id": 3,
      "published": true,
      "name": "course 3"
    },
    {
      "_id": 4,
      "published": true,
      "name": "course 4"
    }
  ]
}

How do I query all chapters with the published course (published=true) and the author is "John"?

Upvotes: 0

Views: 44

Answers (1)

Indraraj26
Indraraj26

Reputation: 1966

You can use $match, $lookup then $group by author then simply $filter it based on published

db.chapters.aggregate([
  {
    $match: {
      author: "John"
    }
  },
  {
    "$lookup": {
      "from": "courses",
      "localField": "course_id",
      "foreignField": "_id",
      "as": "course"
    }
  },
  {
    $project: {
      _id: 1,
      course: {
        $first: "$course"
      },
      author: 1
    }
  },
  {
    $group: {
      _id: "$author",
      courseChapters: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $project: {
      courseChapters: {
        $filter: {
          input: "$courseChapters",
          as: "cc",
          cond: {
            $eq: [
              "$$cc.course.published",
              true
            ]
          }
        }
      }
    }
  }
])

Output

[
  {
    "_id": "John",
    "courseChapters": [
      {
        "_id": 10,
        "author": "John",
        "course": {
          "_id": 1,
          "name": "course 1",
          "published": true
        }
      },
      {
        "_id": 40,
        "author": "John",
        "course": {
          "_id": 4,
          "name": "course 4",
          "published": true
        }
      }
    ]
  }
]

Mongoplayground: https://mongoplayground.net/p/x-XghXzZUk4

Upvotes: 1

Related Questions