Mongo Query to fetch documents on the basis of same field value and those which are not expired

Question

I have some collection sharing and there are fields sharedWith,expiryTime in sharing collection.

sharedWith field is an array specifies that sharing has been done with these users , it contains the ID for the users.

sharedWith : [NumberLong(11),NumberLong(22),NumberLong(33)]

I need to fetch those documents in which multiple sharing has been done with same user that means output should returns more than one sharing document with the same sharedWith value and those which are not expired:

// condition to check whether document has been expired or not

currentTime < expiryTime // means the sharing document has not been expired

currentTime : (present time todays)

expiryTime : (expiryTime is a field in sharing collection)

Examples:

A 
{sharedWith : [NumberLong(123),NumberLong(456)],
 expiryTime : ISODate("2021-07-03T06:22:29.021Z")
},
B 
{sharedWith : [NumberLong(456)],
 expiryTime : ISODate("2021-07-03T06:22:29.021Z")
},
C 
{sharedWith : [NumberLong(123456)],
 expiryTime : ISODate("2021-07-03T06:22:29.021Z")
},
D
{sharedWith : [NumberLong(111111)],
 expiryTime : ISODate("2021-06-03T06:22:29.021Z")
},

The output for this case will be only A and B since both have common sharedWith field value NumberLong(456) and are not expired because todays time(currentTime : 1 July) is less than expiryTime.

Note : If for collection B the currentTime >= expiryTime means if it is expired then it should not return any output because in that case document A or C cannot be returned alone since output must contains more than one sharing document with similar sharedWith field value even if it is not expired. Document D is out of scope because it is expired since todays time > expiryTime for D.

How do I update the below query to achieve this. Thankyou very much

db.getCollection('sharing').aggregate([
  {
    $addFields: { time: { $lt: ["$currentTime", "$expiryTime"] } }
  },
  { 
    $match: { time: true } 
  },

   { $group: { 
        // Group by fields to match on sharedWith
        _id: { sharedWith: "$a"},

        // Count number of matching docs for the group
        count: { $sum:  1 },
    
        // Save the _id for matching docs
        docs: { $push: "$_id" }
    }},
    // Limit results to duplicates (more than 1 match) 
    { $match: {
        count: { $gt : 1 }
    }}
]);

This is the final query , but I think it is not working as an AND condition , I want those records only whose sharedWith value is same and whose currentTime < expiryTime

Answer 1

The below query solves the problem... $unwind is used to split array in to individual fields so that $group works on sharedWith

db.getCollection('sharing').aggregate([
  { 
    $match: { "expiryTime":{"$gte": ISODate()}  } 
  },
  { $unwind: "$sharedWith"},
  { $group: { 
       // Group by fields to match on sharedWith
       _id: "$sharedWith",

       // Count number of matching docs for the group
       count: { $sum:  1 },

       // Save the _id for matching docs
       docs: { $push: "$_id" }
    }},
    // Limit results to duplicates (more than 1 match) 
    { $match: {
        count: { $gt : 1 }
    }}
]);