javascript - 在 mongoDB 中获取相邻文档之间的差异

Question

如何使用 javascript 获取 mongoDB 中相邻记录之间的差异？例如，如果我在一个集合中有以下三个文档:

{
    "_id" : ObjectId("50ed90a55502684f440001ac"),
    "time" : ISODate("2013-02-13T15:45:41.148Z")
}

{
    "_id" : ObjectId("50ed90a55502684f440001ad"),
    "time" : ISODate("2013-02-13T15:45:42.148Z")
}

{
    "_id" : ObjectId("50ed90a55502684f440001ae"),
    "time" : ISODate("2013-02-13T15:45:45.148Z")
}

我想取相邻值之间的“时间”字段中的差异来获得:

{
    "_id" : ObjectId("50ed90a55502684f440001ac"),
    "time" : ISODate("2013-02-13T15:45:41.148Z"),
    "time_difference" : null
}

{
    "_id" : ObjectId("50ed90a55502684f440001ad"),
    "time" : ISODate("2013-02-13T15:45:42.148Z"),
    "time_difference" : 1
}

{
    "_id" : ObjectId("50ed90a55502684f440001ae"),
    "time" : ISODate("2013-02-13T15:45:45.148Z"),
    "time_difference" : 3
}

关于如何在 javascript/mongoDB 中有效地执行此操作的任何想法？谢谢。

Answer 1

我不知道七年前问这个问题的时候是不是这样，但这完全可以在聚合框架内解决。假设集合名称是 AdjacentDocument，以下聚合将获得您要查找的结果:

db.AdjacentDocument.aggregate(
    {$sort: {time: 1}},
    {$group: {_id: 0, document: {$push: '$$ROOT'}}},
    {$project: {documentAndPrevTime: {$zip: {inputs: ['$document', {$concatArrays: [[null], '$document.time']}]}}}},
    {$unwind: {path: '$documentAndPrevTime'}},
    {$replaceWith: {$mergeObjects: [{$arrayElemAt: ['$documentAndPrevTime', 0]}, {prevTime: {$arrayElemAt: ['$documentAndPrevTime', 1]}}]}},
    {$set: {time_difference: {$trunc: [{$divide: [{$subtract: ['$time', '$prevTime']}, 1000]}]}}},
    {$unset: 'prevTime'}
);

聚合管道演练

首先，文档从最旧到最新排序。它们被分组到一个文档中，文档存储在一个有序的数组字段中:

{$sort: {time: 1}},
{$group: {_id: 0, document: {$push: '$$ROOT'}}}

/*
{
    "_id" : 0,
    "document" : [
        {
            "_id" : ObjectId("50ed90a55502684f440001ac"),
            "time" : ISODate("2013-02-13T15:45:41.148Z")
        },
        {
            "_id" : ObjectId("50ed90a55502684f440001ad"),
            "time" : ISODate("2013-02-13T15:45:42.148Z")
        },
        {
            "_id" : ObjectId("50ed90a55502684f440001ae"),
            "time" : ISODate("2013-02-13T15:45:45.148Z")
        }
    ]
}
*/

接下来，将之前的时间压缩到文档数组中，创建一个数组[document, previousTime]:

{$project: {documentAndPrevTime: {$zip: {inputs: ['$document', {$concatArrays: [[null], '$document.time']}]}}}}

/*
{
    "_id" : 0,
    "documentAndPrevTime" : [
        [
            {
                "_id" : ObjectId("50ed90a55502684f440001ac"),
                "time" : ISODate("2013-02-13T15:45:41.148Z")
            },
            null
        ],
        [
            {
                "_id" : ObjectId("50ed90a55502684f440001ad"),
                "time" : ISODate("2013-02-13T15:45:42.148Z")
            },
            ISODate("2013-02-13T15:45:41.148Z")
        ],
        [
            {
                "_id" : ObjectId("50ed90a55502684f440001ae"),
                "time" : ISODate("2013-02-13T15:45:45.148Z")
            },
            ISODate("2013-02-13T15:45:42.148Z")
        ]
    ]
}
*/

接下来，展开文档和时间数组，为每个初始文档创建一个文档:

{$unwind: {path: '$documentAndPrevTime'}}

/*
{
    "_id" : 0,
    "documentAndPrevTime" : [
        {
            "_id" : ObjectId("50ed90a55502684f440001ac"),
            "time" : ISODate("2013-02-13T15:45:41.148Z")
        },
        null
    ]
}
{
    "_id" : 0,
    "documentAndPrevTime" : [
        {
            "_id" : ObjectId("50ed90a55502684f440001ad"),
            "time" : ISODate("2013-02-13T15:45:42.148Z")
        },
        ISODate("2013-02-13T15:45:41.148Z")
    ]
}
{
    "_id" : 0,
    "documentAndPrevTime" : [
        {
            "_id" : ObjectId("50ed90a55502684f440001ae"),
            "time" : ISODate("2013-02-13T15:45:45.148Z")
        },
        ISODate("2013-02-13T15:45:42.148Z")
    ]
}
*/

接下来，我们用文档数组元素的值替换文档，并与之前的时间元素合并(如果它是初始索引，则使用 null):

{$replaceWith: {$mergeObjects: [{$arrayElemAt: ['$documentAndPrevTime', 0]}, {prevTime: {$arrayElemAt: ['$documentAndPrevTime', 1]}}]}}

/*
{
    "_id" : ObjectId("50ed90a55502684f440001ac"),
    "time" : ISODate("2013-02-13T15:45:41.148Z"),
    "prevTime" : null
}
{
    "_id" : ObjectId("50ed90a55502684f440001ad"),
    "time" : ISODate("2013-02-13T15:45:42.148Z"),
    "prevTime" : ISODate("2013-02-13T15:45:41.148Z")
}
{
    "_id" : ObjectId("50ed90a55502684f440001ae"),
    "time" : ISODate("2013-02-13T15:45:45.148Z"),
    "prevTime" : ISODate("2013-02-13T15:45:42.148Z")
}
*/

最后，我们通过将 time_difference 设置为两个时间字段的差值并删除临时的 prevTime 字段来更新文档。由于两个日期之间的差异以毫秒为单位，而您的示例使用秒，因此我们通过除以 1000 并 chop 来计算秒数。

{$set: {time_difference: {$trunc: [{$divide: [{$subtract: ['$time', '$prevTime']}, 1000]}]}}},
{$unset: 'prevTime'}

/*
{
    "_id" : ObjectId("50ed90a55502684f440001ac"),
    "time" : ISODate("2013-02-13T15:45:41.148Z"),
    "time_difference" : null
}
{
    "_id" : ObjectId("50ed90a55502684f440001ad"),
    "time" : ISODate("2013-02-13T15:45:42.148Z"),
    "time_difference" : 1
}
{
    "_id" : ObjectId("50ed90a55502684f440001ae"),
    "time" : ISODate("2013-02-13T15:45:45.148Z"),
    "time_difference" : 3
}
*/