Question 118567 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Query with arbitrary number of filter conditions that come from querying the same collection

const score_schema = mongoose.Schema(
  {
    tester_id: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "user",
      required: true,
    },
    test_id: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "test",
      required: true,
    },
    score: {
      type: Number,
      required: true,
    },
  },
  {
    collection: `score`,
    timestamps: true,
  }
);

query 1:

A user is given. First a query to the score model is made to find all tests that this user has taken.
This will result in an arbitrary number of tests and the score of each test.

query 2:

Another query is made to the score model to find all documents where the test_id matches the test_id returned from the above query
and where the score of each test is greater than or equal to the score of the test_id returned from the query above.

This essentially means that the arbitrary number of documents returned from query 1 will be the number of filter conditions to query 2.

Problem:

Can the above 2 queries be combined into 1 query and thus one round trip to the MongoDB API? If not, then a solution of 2 separate queries is also acceptable.

const dummy_data = [
  {
    tester_id: "1",
    test_id: "1",
    score: 40
  },
  {
    tester_id: "1",
    test_id: "2",
    score: 50
  },
   {
    tester_id: "1",
    test_id: "3",
    score: 70
  },
  {
    tester_id: "2",
    test_id: "1",
    score: 50
  },
  {
    tester_id: "3",
    test_id: "2",
    score: 20
  },
   {
    tester_id: "3",
    test_id: "3",
    score: 60
  },
  {
    tester_id: "7",
    test_id: "5",
    score: 40
  },
  {
    tester_id: "8",
    test_id: "4",
    score: 50
  },
   {
    tester_id: "9",
    test_id: "4",
    score: 70
  },
]

EDITED:

The output should have the same schema as the original collection, score_schema.

For example, given tester_id of 1, the output should be:

[
  {
    tester_id: "2",
    test_id: "1",
    score: 50
  }
]

Explanation:

The tester_id of 1 took a total of 3 tests. There is only one document where another tester_id took a test equaling to one of the three taken by tester_id of 1 and had a score greater than the score generated by tester_id of 1.

The output should not include documents with the original tester_id. In this case, it is tester_id 1.

Tags:

4

Answers


  1. 0

    You can use the aggregate query with $lookup

    db.collection.aggregate([
  {
    $match: {
      "tester_id": "1"
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        "score": "$score",
        "tester_id": "$tester_id"
      },
      "localField": "tester_id",
      "foreignField": "test_id",
      "pipeline": [
        {
          $match: {
            $expr: {
              "$and": [
                {
                  $ne: [
                    "$tester_id",
                    "$$tester_id"
                  ]
                },
                {
                  $gte: [
                    "$score",
                    "$$score"
                  ]
                }
              ]
            }
          }
        },
        
      ],
      "as": "result"
    }
  },
  {
    "$unwind": "$result"
  },
  {
    $group: {
      "_id": null,
      "result": {
        $push: "$result"
      }
    }
  },
  {
    "$replaceWith": {
      "$mergeObjects": "$result"
    }
  },
  {
    $project: {
      "_id": 0
    }
  }
])

    Output:

    [
  {
    "score": 50,
    "test_id": "1",
    "tester_id": "2"
  }
]

    Playground

    Login or Signup to reply.
  2. 0

    Here comes a detailed aggregation that handles the data

    If I understood all the requirements correctly

    [{$facet: {
 tests_per_tester: [
  {
   $match: {
/***   select the tester here **/
    tester_id: '1'
   }
  },
  {
   $group: {
    _id: '$tester_id',
    tests: {
     $push: '$$ROOT'
    }
   }
  }
 ],
 scores_by_test: [
  {
   $group: {
    _id: '$test_id',
    scores: {
     $push: '$score'
    }
   }
  }
 ]
}}, {$unwind: {
 path: '$tests_per_tester'
}}, {$unwind: {
 path: '$tests_per_tester.tests'
}}, {$unwind: {
 path: '$scores_by_test'
}}, {$unwind: {
 path: '$scores_by_test.scores'
}}, {$match: {
 $expr: {
  $and: [
   {
    $eq: [
     '$tests_per_tester.tests.test_id',
     '$scores_by_test._id'
    ]
   },
   {
    $gte: [
     '$tests_per_tester.tests.score',
     '$scores_by_test.scores'
    ]
   }
  ]
 }
}}]

    The $facet step divides the data into two categories: testers and tests, while the rest of the query is just a series of $unwinds and a $match to give the desired results.

    Mongo playground

    Login or Signup to reply.
  3. 0

    Here’s one way to do it. (Comments are in the query.)

    db.score.aggregate([
  { // the tester of interest
    "$match": { "tester_id": "1" }
  },
  {
    "$lookup": {
      // lookup by test_id
      "from": "score",
      "localField": "test_id",
      "foreignField": "test_id",
      "let": { "myId": "$tester_id", "myScore": "$score" },
      "pipeline": [
        { // only return docs of different tester
          // and higher score
          "$match": {
            "$expr": {
              "$and": [
                { "$ne": [ "$tester_id", "$$myId" ] },
                { "$gt": [ "$score", "$$myScore" ] }
              ]
            }
          }
        }
      ],
      "as": "higherScores"
    }
  },
  { // only keep non-empty higherScores
    "$match": {
      "$expr": { "$gt": [ { "$size": "$higherScores" }, 0 ] }
    }
  },
  { // only field we care about now
    "$project": { "higherScores": 1 }
  },
  { // might be more than one
    "$unwind": "$higherScores"
  },
  { // hoist it to ROOT
    "$replaceWith": "$higherScores"
  },
  { // don't want _id
    "$unset": "_id"
  }
])

    Try it on mongoplayground.net.

    Login or Signup to reply.
  4. 0

    Another way to do it without lookup is using $group, for example:

    db.score.aggregate([
  {
    $group: {
      _id: "$test_id",
      highestScore: {$max: "$score"},
      tester_id: {$push: "$tester_id"},
      results: {
        $push: {score: "$score", "tester_id": "$tester_id"}
      },
      ourTester: {
        $push: {score: "$score", "tester_id": "$tester_id"}
      }
    }
  },
  {$match: {"tester_id": userId}},
  {
    $project: {
      ourTester: {
        $filter: {
          input: "$ourTester",
          as: "item",
          cond: {$eq: ["$$item.tester_id", userId]}
        }
      },
      results: {
        $filter: {
          input: "$results",
          as: "item",
          cond: {$eq: ["$$item.score", "$highestScore"]}}
      }
    }
  },
  {
    $project: {
      ourTester: {"$arrayElemAt": ["$ourTester", 0]},
      highest: {"$arrayElemAt": ["$results", 0]}
    }
  },
  {
    $match: {
      $expr: {$gt: ["$highest.score", "$ourTester.score"]}
    }
  },
  {
    $project: {
      score: "$highest.score",
      tester_id: "$highest.tester_id",
      test_id: "$res._id"
    }
  }
])

    As you can see here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search