'How do I display the min/max value alongside its corresponding unique id in MongoDB?

3 documents from my collection looks like below.

{'r1': {'t1': 600,
        't2': 300,
        'efficiency': 67},
 'r2': {'t1': 800,
        't2': 400,
        'efficiency': 71},
 'userId': ObjectId('5fedca01bbd56000131faaaa')},
{'r1': {'t1': 700,
        't2': 640,
        'efficiency': 60},
 'r2': {'t1': 200,
        't2': 150,
        'efficiency': 70},
 'userId': ObjectId('5fedca01bbd56000131fbbbb')},
{'r1': {'t1': 900,
        't2': 400,
        'efficiency': 75},
 'r2': {'t1': 820,
        't2': 380,
        'efficiency': 80},
 'userId': ObjectId('5fedca01bbd56000131fcccc')}

My intension is to retrieve the min-value + the unique userId for all the fields except efficiency, where I want the max-value + the unique userId. So, my intension is to achieve something like below:

{
  'userId': ObjectId('5fedca01bbd56000131faaaa'), 'r1t1': 600
  'userId': ObjectId('5fedca01bbd56000131faaaa'), 'r1t2': 300
  'userId': ObjectId('5fedca01bbd56000131fbbbb'), 'r2t1': 200
  'userId': ObjectId('5fedca01bbd56000131fbbbb'), 'r2t2': 150
  'userId': ObjectId('5fedca01bbd56000131fcccc'), 'r1eff': 75
  'userId': ObjectId('5fedca01bbd56000131fcccc'), 'r2eff': 80
}

I've managed to get the min/max-values with the code below. However, how do I combine these values with the corresponding userId?

group1 = {
    "$group": {
        "_id": {},
        "r1t1": {"$min": "$r1.t1"},
        "r1t2": {"$min": "$r1.t2"},
        "r2t1": {"$min": "$r2.t1"},
        "r2t2": {"$min": "$r2.t2"},
        "r1eff": {"$max": "$r1.efficiency"},
        "r2eff": {"$max": "$r2.efficiency"},
    }
}

If I add "_id": "$userId", I only get an output with 1 userId, but I was hoping for 6 unique userIds.

What do I need to add to achieve the correct output? Thank you in advance!

mongodbaggregation-framework


Solution 1:[1]

Query

  • mongodb can sort also documents(goes on fields one by one with order they appear), this is the way used here to save also the user that has this min/max value

PlayMongo

aggregate(
[{"$group": 
    {"_id": null,
      "r1t1": {"$min": {"min": "$r1.t1", "userId": "$userId"}},
      "r1t2": {"$min": {"min": "$r1.t2", "userId": "$userId"}},
      "r2t1": {"$min": {"min": "$r2.t1", "userId": "$userId"}},
      "r2t2": {"$min": {"min": "$r2.t2", "userId": "$userId"}},
      "r1eff": {"$max": {"max": "$r1.efficiency", "userId": "$userId"}},
      "r2eff": {"$max": {"max": "$r2.efficiency", "userId": "$userId"}}}},
  {"$unset": ["_id"]}])

Results

  • results are almost like the one you wanted, if you need exactly like you asked, it can be changed
[{
  "r1t1": {
    "min": 600,
    "userId": ObjectId("5fedca01bbd56000131faaaa")
  },
  "r1t2": {
    "min": 300,
    "userId": ObjectId("5fedca01bbd56000131faaaa")
  },
  "r2t1": {
    "min": 200,
    "userId": ObjectId("5fedca01bbd56000131fbbbb")
  },
  "r2t2": {
    "min": 150,
    "userId": ObjectId("5fedca01bbd56000131fbbbb")
  },
  "r1eff": {
    "max": 75,
    "userId": ObjectId("5fedca01bbd56000131fcccc")
  },
  "r2eff": {
    "max": 80,
    "userId": ObjectId("5fedca01bbd56000131fcccc")
  }
}]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions