Here, I have multiple fields from multiple tables those values needs to compared and need to display desired result.

SQL QUERY:

select pd.service_id,ps.service_id from player pd, service ps where pd.subject_id=ps.subject_id and pd.service_id = ps.service_id

Mongo query:

db.player.aggregate([
        {
          "$lookup":{
            "from":"service",
            "localField":"player.subject_id",
            "foreignField":"subject_id",
            "as":"ps"
          }
        },
        {
          "$unwind":"$ps"
        },
        {
          "$match":{
           "service_id":{
            "$eq": "ps.service_id"
           }
          }
        }  
      ];

sample input records:

player:

[{subject_id:23,service_id:1},{subject_id:76,service_id:9}]

service:

[{subject_id:76,service_id:9},{subject_id:99,service_id:10}]

The match is not working. I have to match service_id's of both collections. Need to get matched records. But not able to see any result. Can anyone please help me to find out the mistake...

CodePudding user response：

In your query, if you want to compare 2 values from the document itself, you need to use $expr operator

{
    "$match":{
       "$expr":{
            "$eq": ["$service_id", "$ps.service_id"]
       }
    }
} 

MongoPlayground

Alternative solution: You need to use Uncorrelated sub-query to "* join" with 2 o more conditions

db.player.aggregate([
  {
    "$lookup": {
      "from": "service",
      "let": {
        subject_id: "$subject_id",
        service_id: "$service_id"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$subject_id",
                    "$subject_id"
                  ]
                },
                {
                  $eq: [
                    "$$service_id",
                    "$service_id"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "ps"
    }
  },
  // Remove non matched results
  {
    $match: {
      "ps.0": {
        $exists: true
      }
    }
  },
  // Remove temporal "ps" field
  {
    $addFields: {
      "ps": "$$REMOVE"
    }
  }
])

MongoPlayground