db.Product.aggregate([
  {
    $match: {
      $or: [
        {
          "message": {
            $regex: "he"
          }
        },
        {
          "field3": {
            $regex: "s"
          }
        }
      ]
    }
  },
  {
    $lookup: {
      from: "User",
      localField: "_id",
      foreignField: "favorite",
      as: "join"
    }
  },
  {
    $addFields: {
      favorited: {
        $cond: [
          {
            $in: [
              "$_id",
              "$join.favorite"
            ]
          },
          true,
          false
        ]
      }
    }
  }
])

I am confused as to why this doesn't work. It should return true, but I am not getting the expected result. I can make it work with $function, but I can't use $function since the MongoDB version I am using is 4.2 and not 4.4, which is required for $function.

[
  {
    "_id": "60508eeb52a2ab598a013bb9",
    "favorited": false,
    "field1": false,
    "field2": 1,
    "field3": "something",
    "join": [],
    "message": "hello"
  },
  {
    "_id": "60508f4152a2ab598a013bbb",
    "favorited": true,
    "field1": false,
    "field2": 1,
    "field3": "s1",
    "join": [
      {
        "_id": ObjectId("60508f4152a2ab598a013bbb"),
        "aId": ObjectId("60508f2852a2ab598a013bba"),
        "favorite": [
          "60508f4152a2ab598a013bbb",
          "60508f4552a2ab598a013bbc"
        ]
      },
      {
        "_id": ObjectId("60508f4552a2ab598a013bbc"),
        "aId": ObjectId("60508f2852a2ab598a013bba"),
        "favorite": [
          "60508f4152a2ab598a013bbb",
          "60508f4552a2ab598a013bbc"
        ]
      },
      {
        "_id": ObjectId("605090a452a2ab598a013bbd"),
        "aId": ObjectId("60508eeb52a2ab598a013bb9"),
        "favorite": [
          "60508f4152a2ab598a013bbb",
          "60508f4552a2ab598a013bbc"
        ]
      }
    ],
    "message": "hello"
  },
  {
    "_id": "60508f2852a2ab665a013bba",
    "favorited": false,
    "field1": false,
    "field2": 1,
    "field3": "s1",
    "join": [],
    "message": "hello"
  }
]

I am expecting the above result.

https://mongoplayground.net/p/kM80POZ8rSq

Expressions can include field paths, literals, system variables, expression objects, and expression operators. Expressions can be nested.

https://www.mongodb.com/docs/manual/meta/aggregation-quick-reference/#std-label-aggregation-expressions

CodePudding user response：

If you try to read the value for favorite in join, it returns:

{
  "join": [
    "favorite": [
      "value1",
      "value2"
    ]
  ]
}

So when you query with join.favorite, it returns a nested array:

[ ["value1", "value2"] ]

You need to flatten the array before performing $in comparison via $reduce.

{
  $reduce: {
    input: "$join",
    initialValue: [],
    in: {
      $concatArrays: [
        "$$value",
        "$$this.favorite"
      ]
    }
  }
}

Complete query

db.testA.aggregate([
  {
    $match: {
      $or: [
        {
          "message": {
            $regex: "he"
          }
        },
        {
          "field3": {
            $regex: "s"
          }
        }
      ]
    }
  },
  {
    $lookup: {
      from: "testB",
      localField: "_id",
      foreignField: "favorite",
      as: "join"
    }
  },
  {
    $addFields: {
      favorite: {
        $cond: [
          {
            $in: [
              "$_id",
              {
                $reduce: {
                  input: "$join",
                  initialValue: [],
                  in: {
                    $concatArrays: [
                      "$$value",
                      "$$this.favorite"
                    ]
                  }
                }
              }
            ]
          },
          true,
          false
        ]
      }
    }
  }
])

Sample Mongo Playground