Mongodb aggregation lookup with conditions

by

First of all, it is all_category_id, not category_id. Secondly, you don’t link articles – all documents will have exactly the same article_category array. Lastly, you probably want to filter out articles that don’t have matched category. The conditional pipeline should look more like this:

db.article.aggregate([
  { $match: {
      title: { $regex: /example/ }
  } },
  { $lookup: {
    from: "article_category",
    let: {
      article_id: "$article_id"
    },
    pipeline: [
      { $match: {
          $expr: { $and: [
              { $in: [ 8, "$all_category_id" ] },
              { $eq: [ "$article_id", "$$article_id" ] }
          ] }
      } }
    ],
    as: "article_category"
  } },
  { $match: {
    $expr: { $gt: [
      { $size: "$article_category"},
      0
    ] }
  } }
] )

UPDATE:

If you don’t match article_id, the $lookup will result with identical article_category array to all articles.

Let’s say your article_category collection has another document:

{
  "article_id": 0,
  "all_category_id": [5,8,10]
}

With { $eq: [ "$article_id", "$$article_id" ] } in the pipeline the resulting article_category is 

[ 
  { 
    "article_id" : 2015110920343902, 
    "all_category_id" : [ 5, 8, 10 ] 
  } 
]

without:

[ 
  { 
    "article_id" : 2015110920343902, 
    "all_category_id" : [ 5, 8, 10 ] 
  },
  {
    "article_id": 0,
    "all_category_id": [ 5, 8, 10 ]
  }
]

If the later is what you need, it would be way simpler to make to find requests:

db.article.find({ title: { $regex: /example/ } })

and 

db.article_category.find({ all_category_id: 8 })

More Related Contents:

  1. Retrieve only the queried element in an object array in MongoDB collection
  2. Update MongoDB field using value of another field
  3. Does MongoDB’s $in clause guarantee order
  4. $lookup on ObjectId’s in an array
  5. MongoDB: Combine data from multiple collections into one..how?
  6. Multiple join conditions using the $lookup operator
  7. Return only matched sub-document elements within a nested array
  8. Converting string to date in mongodb
  9. Check if every element in array matches condition
  10. MongoDB nested lookup with 3 levels
  11. MongoDB Full and Partial Text Search
  12. Matching ObjectId to String for $graphLookup
  13. Mongodb Join on _id field from String to ObjectId
  14. How to get back the Original document back after aggregation
  15. Mongo sort on a calculated condition
  16. MongoDB lookup when foreign field is an array of objects
  17. MongoDB: Calculate dwell time between every status value change
  18. MongoDB: how to parse date in 3.6 mongoDb version?
  19. Search on multiple collections in MongoDB
  20. Using stored JavaScript functions in the Aggregation pipeline, MapReduce or runCommand
  21. MongoDB – The argument to $size must be an Array, but was of type: EOO / missing
  22. convert iso date to timestamp in mongo query
  23. Remove Duplicates from MongoDB
  24. Using $slice operator to get last element of array
  25. How to access overall document count during arithmetic aggregation expression
  26. count array occurrences across all documents with mongo
  27. Mongodb, aggregate query with $lookup
  28. mongodb aggregate query isn’t returning proper sum on using $sum
  29. Return the last “true” value for each group
  30. MongoDB aggregate within daily grouping [duplicate]

Leave a Comment