// mongoose schema format

mongoose.Schema(
  {
    businessID: { type: String, index: true },
    userID: { type: String, index: true },
    userBusinessID: { type: Number, index: true },
    data: { type: String },
  }
);

db.data.find({})

[
  {
    businessID: 'B1',
    userID: 'U1',
    userBusinessID: 1,
    data: "hello1"
  },
  {
    businessID: 'B1',
    userID: 'U1',
    userBusinessID: 2,
    data: "hello2"
  },
  {
    businessID: 'B2',
    userID: 'U1',
    userBusinessID: 1,
    data: "hello4"
  },
  {
    businessID: 'B1',
    userID: 'U2',
    userBusinessID: 1,
    data: "hello5"
  },
]

// Expected return data of userID "U1"

[
  {
    businessID: 'B1',
    userID: 'U1',
    userBusinessID: 2, // notice that B1 & U1 has two docs, but the pipeline returns the doc with higher value
    data: "hello2"
  },
  {
    businessID: 'B2',
    userID: 'U1',
    userBusinessID: 1,
    data: "hello4"
  }
]

It can be worked on in application layer but I can't seem to think of any aggretation pipeline combination that can help with the behavior of:

1. match
2. sort by a particular field
3. return the highest matched field document if there are common matches

CodePudding user response：

One option is:

db.collection.aggregate([
  {$match: {userID: "U1"}},
  {$sort: {userBusinessID: -1}},
  {$group: {
      _id: {businessID: "$businessID", userID: "$userID"},
      data: {$first: "$$ROOT"}
  }},
  {$replaceRoot: {newRoot: "$data"}}
])

See how it works on the playground example