I have a problem at hand for which I am having a hard time formulating a solution. I have to write an aggregation in MongoDB.

Schema to be queried:

{
   "_id": ObjectId,
   "foreignKey": ObjectId,
   "createdAt": ISODate,
   "updatedAt": ISODate
}

• Given a set of stringified foreignKey OIds, generate an object with the key being a foreignId and the value being Boolean. The logic to decide the value is following:

  • If the count of documents for a given foreignKey is greater than 0 in the last x days, store foreignKey: true
  • else store foreignKey: true

• For example:

  • foreignKeys = ['6001','6002', '6003'] // these are supposed to be OIds, taking small strings for ease of explanation
  • Docs in collection:

{
  "_id": <unique OId>,
  "foreignKey": "6001",
  "createdAt": "2020-08-30T12:00:00.948Z",
  "updatedAt": "2020-08-30T16:00:00.948Z",
},
{
  "_id": <unique OId>,
  "foreignKey": "6001",
  "createdAt": "2020-08-29T12:00:00.948Z",
  "updatedAt": "2020-08-30T16:00:00.948Z",
},
{
  "_id": <unique OId>,
  "foreignKey": "6002",
  "createdAt": "2020-08-30T09:00:00.948Z",
  "updatedAt": "2020-08-30T21:00:00.948Z",
}

• x days is let's say 2 days (then all the above-mentioned docs come into consideration based on current date being "2020-08-31")

• The result should be the following:

{
  "6001":true, // 2 documents that match the conditions
  "6002":true, // 1 document that matches the conditions
  "6003":false // 0 documents that match the conditions
}

How can this be achieved?

CodePudding user response：

Here is a pipeline which return true for all the existing foreignKey:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$foreignKey",
      data: {
        "$addToSet": {
          "k": "$foreignKey",
          "v": true
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "data": {
        "$arrayToObject": "$data"
      }
    }
  },
  {
    "$replaceWith": "$data"
  },
  {
    $group: {
      "_id": "null",
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        "$mergeObjects": "$data"
      }
    }
  }
])

The result is

[
  {
    "6001": true,
    "6002": true
  }
]

You can add a filter as a first step to filter the data based on the created or updated date.

For the missing foreignKeys, if you have a hard array, then you can fill false for each missing foreign key. But if there is an another collection which contains these foreignKeys, then you can add an extra step $lookup (doc) to join the two collections and setting the value to false:

db.ids.aggregate([
  {
    "$lookup": {
      "from": "data",
      "localField": "_id",
      "foreignField": "foreignKey",
      "as": "data"
    }
  },
  {
    "$project": {
      "data": {
        $cond: {
          if: {
            $gte: [
              {
                $size: "$data"
              },
              1
            ]
          },
          then: true,
          else: false
        }
      }
    }
  },
  {
    $group: {
      "_id": "null",
      data: {
        "$addToSet": {
          "k": "$$ROOT._id",
          "v": "$$ROOT.data"
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "data": {
        "$arrayToObject": "$data"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        "$mergeObjects": "$data"
      }
    }
  }
])

Link to Mongo Playground