How to use current field in second $match?

user_18119418 0
Viewed 54

Let's say i have 2 collections

// Post collection:
[
    {
      "_id": "somepost1",
      "author": "firstuser",
      "title": "First post"
    },
    {
      "_id": "somepost2",
      "author": "firstuser",
      "title": "Second post"
    },
    {
      "_id": "somepost3",
      "author": "firstuser",
      "title": "Third post"
    }
]

// User collection:

[
    {
      "_id": "firstuser",
      "nickname": "John",
      "posts": {
        "voted": []
      }
    },
    {
      "_id": "seconduser",
      "nickname": "Bob",
      "posts": {
        "voted": [
          {
            "_id": "somepost1",
            "vote": "1"
          },
          {
            "_id": "somepost3",
            "vote": "-1"
          }
        ]
      }
    }
  ]

And i need to get this result:

[
  {
    "_id": "somepost1",
    "author": {
      "_id": "firstuser",
      "nickname": "John"
    },
    "title": "First post",
    "myvote": "1"
  },
  {
    "_id": "somepost2",
    "author": {
      "_id": "firstuser",
      "nickname": "John"
    },
    "title": "Second post",
    "voted": "0"
  },
  {
    "_id": "somepost3",
    "author": {
      "_id": "firstuser",
      "nickname": "John"
    },
    "title": "Third post",
    "myvote": "-1"
  }
]

How can i make a request with aggregation, which will display this output with dynamic _id of elements? I have problem with using current _id of post in second $match and setting "myvote" to 0 if there are no element in "posts.voted" associated with current post.

Here what i've tried: https://mongoplayground.net/p/v70ZUioVSpQ

db.post.aggregate([
  {
    $match: {
      author: "firstuser"
    }
  },
  {
    $lookup: {
      from: "user",
      localField: "author",
      foreignField: "_id",
      as: "author"
    }
  },
  {
    $addFields: {
      author: {
        $arrayElemAt: [
          "$author",
          0
        ]
      }
    }
  },
  {
    $lookup: {
      from: "user",
      localField: "_id",
      foreignField: "posts.voted._id",
      as: "Results"
    }
  },
  {
    $unwind: "$Results"
  },
  {
    $unwind: "$Results.posts.voted"
  },
  {
    $match: {
      "Results.posts.voted._id": "ID OF CURRENT POST"
    }
  },
  {
    $project: {
      _id: 1,
      author: {
        _id: 1,
        nickname: 1
      },
      title: 1,
      myvote: "$Results.posts.voted.vote"
    }
  }
])
aggregation-framework mongodb-query mongodb
1 Answers

From the $match docs:

The query syntax is identical to the read operation query syntax

The query syntax does not allow usage of document values. which is what you're trying to do.

What we can do is use $expr within the $match stage, this allows us to use aggregation oprerators, thus also giving access to the document values. like so:

{
    $match: {
        $expr: {
            $eq: ['$Results.posts.voted._id', '$_id'],
        }
    },
},
user_107703700
Related