MongoDB聚合/展开/组/项目查询组合 [英] MongoDB aggregation/unwind/group/project query combination

问题描述

我有以下格式的记录集:

I have records in a collection of the following format:

"_id" : "2013-05-23",
    "authors_who_sold_books" : [
        {
            "id" : "Charles Dickens",
            "num_sold" : 1,
            "customers" : [
                {
                   "time_bought" : 1368627290,
                   "customer_id" : 9715923
                }
            ]
        },
        {
            "id" : "JRR Tolkien",
            "num_sold" : 2,
            "customers" : [
                {
                    "date_bought" : 1368540890,
                    "customer_id" : 9872345
                },
                {
                    "date_bought" : 1368537290,
                    "customer_id" : 9163893
                }
            ]
        }
    ]
}

每个日期都有一条记录，其中许多记录将包含同一位作者.我正在返回以下查询:

There is a record for each date, many of which will contain the same author. I'm after a query that returns the following:

{
    "_id" : "Charles Dickens",
    "num_sold" : 235,
    "customers" : [
        {
            "date_bought" : 1368627290,
            "customer_id" : 9715923
        },
        {
            "date_bought" : 1368622358,
            "customer_id" : 9876234
        },
        etc...
    ]
}

我尝试了汇总，分组，展开和项目的各种组合，但仍然不能完全达到目标，真的很感谢任何建议.

I've tried various combinations of aggregate, group, unwind and project but still can't quite get there and would really appreciate any suggestions.

为了加分，我实际上是使用Ruby gem进行此操作的，因此特定于此的代码会很棒.但是，我可以转换普通的MongoDB查询语言.

For extra points, I'm actually doing this using the Ruby gem, so code specific to this would be great. I can convert normal MongoDB query language, however.

推荐答案

我获取了您的示例数据，对第二个文档进行了一些修改，然后将其添加到测试集合中.我使用的文档如下:

I took your sample data, slightly modified for a 2nd document, and then added them to a test collection. The documents I used are as follows:

{
    "_id" : "2013-05-23",
    "authors_who_sold_books" : [
        {
            "id" : "Charles Dickens",
            "num_sold" : 1,
            "customers" : [
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                }
            ]
        },
        {
            "id" : "JRR Tolkien",
            "num_sold" : 2,
            "customers" : [
                {
                    "date_bought" : 1368540890,
                    "customer_id" : 9872345
                },
                {
                    "date_bought" : 1368537290,
                    "customer_id" : 9163893
                }
            ]
        }
    ]
}
{
    "_id" : "2013-05-21",
    "authors_who_sold_books" : [
        {
            "id" : "Charles Dickens",
            "num_sold" : 3,
            "customers" : [
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                },
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                },
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                }
            ]
        },
        {
            "id" : "JRR Tolkien",
            "num_sold" : 1,
            "customers" : [
                {
                    "date_bought" : 1368540890,
                    "customer_id" : 9872345
                }
            ]
        }
    ]
}

现在，为了获得您期望的结果，我使用了聚合框架并运行了该查询:

Now, to get your expected results, I used the aggregation framework and ran this query:

db.collection.aggregate([
    {
        // First we unwind all the authors that sold books
        $unwind: '$authors_who_sold_books',
    },
    {
        // Next, we unwind each of the customers that purchased a book
        $unwind: '$authors_who_sold_books.customers'
    },
    {
        // Now we group them by "Author Name" (hoping they are unique!)
        $group: {
            _id: '$authors_who_sold_books.id',
            // Increment the number sold by each author
            num_sold: {
                $sum: 1
            },
            // Add the customer data to the array
            customers: {
                $push: '$authors_who_sold_books.customers'
            }
        }
    }
]);

我试图记录上面的代码，这样使它更有意义.基本上，它将两次展开数据以为作者的每次销售创建一个文档.首先用authors_who_sold_books展开，然后展开authors_who_sold_books.customers.

I tried to document the above code, so that way it made a bit more sense. Basically, it's unwinding the data twice to create a document for each sale by author. First unwind by authors_who_sold_books, then unwinding authors_who_sold_books.customers.

下一步是将他们分组，并将所有客户推入客户数组，并为我们拥有的每个展开文档将num_sold递增1.

The next step was just grouping them up and pushing all the customers into the customers array, and incrementing the num_sold by 1 for every unwinded document we had.

结果显示如下:

{
    "result" : [
        {
            "_id" : "JRR Tolkien",
            "num_sold" : 3,
            "customers" : [
                {
                    "date_bought" : 1368540890,
                    "customer_id" : 9872345
                },
                {
                    "date_bought" : 1368537290,
                    "customer_id" : 9163893
                },
                {
                    "date_bought" : 1368540890,
                    "customer_id" : 9872345
                }
            ]
        },
        {
            "_id" : "Charles Dickens",
            "num_sold" : 4,
            "customers" : [
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                },
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                },
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                },
                {
                    "time_bought" : 1368627290,
                    "customer_id" : 9715923
                }
            ]
        }
    ],
    "ok" : 1
}

希望这可以帮助您找出真正的解决方案:)

Hope this helps you figure out your real solution :)

这篇关于MongoDB聚合/展开/组/项目查询组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！