在mongoDB中按日期查询文档的最新版本 [英] Query for latest version of a document by date in mongoDB

问题描述

我试图找到一个mongoDB脚本，该脚本将查看一个集合，在该集合中同一文档有多个记录，并且仅向我提供每个文档的最新版本作为结果集.

I am trying to find a mongoDB script which will look at a collection where there are multiple records of the same document and only provide me with the latest version of each document as a result set.

我无法用英语更好地解释它，但也许下面的这个小SQL可能会进一步解释它.我希望每个文档都按transaction_reference，但只需要最新的日期版本(object_creation_date).

I cannot explain it in English any better than above but maybe this little SQL below might explain it further. I want each document by transaction_reference but only the latest dated version (object_creation_date).

select 
    t.transaction_reference, 
    t.transaction_date, 
    t.object_creation_date,
    t.transaction_sale_value
from MyTable t
inner join (
    select 
        transaction_reference, 
        max(object_creation_date) as MaxDate
    from MyTable
    group by transaction_reference
) tm 
    on t.transaction_reference = tm.transaction_reference 
    and t.object_creation_date = tm.MaxDat

同一文档有多个版本的原因是因为我要存储事务的每次迭代.第一次收到文件时，它可能在UNPAID的transaction_status中，然后再次收到同一笔交易，而这次的transaction_status是PAID.

The reason why there are multiple versions of the same document is because I want to store each iteration of a transaction. The first time I receive a document, it may be in transaction_status of UNPAID then I receive the same transaction again and this time the transaction_status is PAID.

一些分析将求和所有唯一交易，而其他一些分析可能是测量状态为UNPAID的文档与下一个PAID之间的时间距离.

Some analysis will be to SUM all unique transactions whereas some other analysis may be to measure the time distance between a document with status UNPAID and the next of PAID.

根据请求，以下是两个文档:

As per request, here are two documents:

{
"_id": {
    "$oid": "579aa337f36d2808839a05e8"
},
"object_class": "Goods & Services Transaction",
"object_category": "Revenue",
"object_type": "Transaction",
"object_origin": "Sage One",
"object_origin_category": "Bookkeeping",
"object_creation_date": "2016-07-05T00:00:00.201Z",
"party_uuid": "dfa1e80a-5521-11e6-beb8-9e71128cae77",
"connection_uuid": "b945bd7c-7988-4d2a-92f5-8b50ab218e00",
"transaction_reference": "SI-1",
"transaction_status": "UNPAID",
"transaction_date": "2016-06-16T00:00:00.201Z",
"transaction_due_date": "2016-07-15T00:00:00.201Z",
"transaction_currency": "GBP",
"goods_and_services": [
    {
        "item_identifier": "PROD01",
        "item_name": "Product One",
        "item_quantity": 1,
        "item_gross_unit_sale_value": 1800,
        "item_revenue_category": "Sales Revenue",
        "item_net_unit_cost_value": null,
        "item_net_unit_sale_value": 1500,
        "item_unit_tax_value": 300,
        "item_net_total_sale_value": 1500,
        "item_gross_total_sale_value": 1800,
        "item_tax_value": 300
    }
],
"transaction_gross_value": 1800,
"transaction_gross_curr_value": 1800,
"transaction_net_value": 1500,
"transaction_cost_value": null,
"transaction_payments_value": null,
"transaction_payment_extras_value": null,
"transaction_tax_value": 300,
"party": {
    "customer": {
        "customer_identifier": "11",
        "customer_name": "KP"
    }
}
}

和第二版现在可以付款

{
"_id": {
    "$oid": "579aa387f36d2808839a05ee"
},
"object_class": "Goods & Services Transaction",
"object_category": "Revenue",
"object_type": "Transaction",
"object_origin": "Sage One",
"object_origin_category": "Bookkeeping",
"object_creation_date": "2016-07-16T00:00:00.201Z",
"party_uuid": "dfa1e80a-5521-11e6-beb8-9e71128cae77",
"connection_uuid": "b945bd7c-7988-4d2a-92f5-8b50ab218e00",
"transaction_reference": "SI-1",
"transaction_status": "PAID",
"transaction_date": "2016-06-16T00:00:00.201Z",
"transaction_due_date": "2016-07-15T00:00:00.201Z",
"transaction_currency": "GBP",
"goods_and_services": [
    {
        "item_identifier": "PROD01",
        "item_name": "Product One",
        "item_quantity": 1,
        "item_gross_unit_sale_value": 1800,
        "item_revenue_category": "Sales Revenue",
        "item_net_unit_cost_value": null,
        "item_net_unit_sale_value": 1500,
        "item_unit_tax_value": 300,
        "item_net_total_sale_value": 1500,
        "item_gross_total_sale_value": 1800,
        "item_tax_value": 300
    }
],
"transaction_gross_value": 1800,
"transaction_gross_curr_value": 1800,
"transaction_net_value": 1500,
"transaction_cost_value": null,
"transaction_payments_value": null,
"transaction_payment_extras_value": null,
"transaction_tax_value": 300,
"party": {
    "customer": {
        "customer_identifier": "11",
        "customer_name": "KP"
    }
}
}

感谢您的支持，马特

推荐答案

如果我正确理解了这个问题，则可以使用类似的方法

If I understand the question correctly you could use something like this

db.getCollection('yourTransactionsCollection').aggregate([
    {
        $sort: {
            "transaction_reference": 1,
            "object_creation_date": -1
        }
    },
    {
        $group: {
            _id: "$transaction_reference",
            "transaction_date": { $first: "$transaction_date" },
            "object_creation_date": { $first: "$transaction_date" },
            "transaction_sale_value": { $first: "$transaction_sale_value" }
        }
    }
])

其输出结果如下所示

{
    "_id" : "SI-1",
    "transaction_date" : "2016-06-16T00:00:00.201Z",
    "object_creation_date" : "2016-06-16T00:00:00.201Z",
    "transaction_sale_value" : null
}

请注意，您可以将$sort更改为仅包含object_creation_date，但是我同时包含了transaction_reference和object_creation_date，因为我认为在它们两者上创建一个复合索引而不是仅仅创建一个索引是有意义的.创建日期.根据您的索引进行调整，以使$sort命中一个.
另外，没有文档字段transaction_sale_value，因此在结果中为它的null.也许您错过了它，或者只是不在示例文档中，但是我认为您已经明白了这个想法，可以根据需要进行调整.

Note that you can change the $sort to just include the object_creation_date but I included both transaction_reference and object_creation_date as I think it would make sense to create a composite index on both of them instead of just the creation date. Adjust that according to your indexes so that the $sort will hit one.
In addition there was no document field transaction_sale_value hence the null for it in the result. Maybe you missed that or it is just not in your sample documents but I think you get the idea and can adjust it to your needs.

这篇关于在mongoDB中按日期查询文档的最新版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！