Mongo Query 根据相同的字段值和未过期的字段值获取文档 [英] Mongo Query to fetch documents on the basis of same field value and those which are not expired

问题描述

我有一些收藏分享，分享收藏中有sharedWith,expiryTime字段.

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

sharedWith 字段是一个数组，指定已与这些用户共享，它包含用户的 ID.

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)]

我需要获取那些与同一用户进行了多次共享的文档，这意味着输出应该返回多个具有相同 sharedWith 值的共享文档和未过期的文档:

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 :(今天的当前时间)

currentTime : (present time todays)

expiryTime : (expiryTime 是共享集合中的一个字段)

expiryTime : (expiryTime is a field in sharing collection)

示例:

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")
},

这种情况的输出将只有 A 和 B，因为两者都有共同的 sharedWith 字段值 NumberLong(456) 并且没有过期，因为今天的时间(currentTime : 1 July)小于 expiryTime.

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.

注意:如果对于集合 B，currentTime >= expiryTime 意味着如果它已过期，则不应返回任何输出，因为在这种情况下，文档 A 或 C 不能单独返回，因为输出必须包含多个共享具有类似 sharedWith 字段值的文档，即使它没有过期.文档 D 超出范围，因为它自今天时间起已过期 >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 }
    }}
]);

这是最终查询，但我认为它不能作为 AND 条件工作，我只想要那些 sharedWith 值相同且 currentTime <;到期时间

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

推荐答案

下面的查询解决了问题...$unwind 用于将数组拆分为单独的字段，以便 $group 在 sharedWith 上工作

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 }
    }}
]);

这篇关于Mongo Query 根据相同的字段值和未过期的字段值获取文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！