MongoDB - 简单的子查询示例 [英] MongoDB - simple sub query example

问题描述

给定数据:

> db.parameters.find({})
{ "_id" : ObjectId("56cac0cd0b5a1ffab1bd6c12"), "name" : "Speed", "groups" : [ "
123", "234" ] }
> db.groups.find({})
{ "_id" : "123", "name" : "Group01" }
{ "_id" : "234", "name" : "Group02" }
{ "_id" : "567", "name" : "Group03" }

我想提供一个 parameter _id 并进行查询以返回 parametersgroups 数组中的所有组> 表.

I would like to supply a parameter _id an make a query return all groups that are within the groups array of the given document in parameters table.

直接的解决方案似乎是在 PyMongo 中进行多个数据库调用:

The straightforward solution seems to make several DB calls in PyMongo:

1. 根据提供的_id从parameters表中获取参数
2. 对于 groups 数组的每个元素，从 groups 集合中选择一个文档

1. Get parameter from parameters table based on the supplied _id
2. For each element of groups array select a document from groups collection

但这会有很多不必要的开销.我觉得在 MongoDB 中必须有更好、更快的方法来执行此操作(无需在数据库中运行自定义 JS).还是应该通过稍微规范化数据(如关系表)来重新构建数据，忽略基于文档的方法?

But this will have so much unnecessary overhead. I feel there must be a better, faster way to do this within MongoDB (without running custom JS in the DB). Or should I re-structure my data by normalising it a little bit (like a table of relationships), neglecting the document-based approach?

再次，请帮我找到一个适用于 PyMongo DB 界面的解决方案

Again, please help me find a solution that would work from PyMongo DB interface

推荐答案

您可以使用聚合框架在单个查询中执行此操作.特别是您需要运行一个使用 $lookup 运算符从 parameters 集合到 groups 集合进行左连接.

You can do this within a single query using the aggregation framework. In particular you'd need to run an aggregation pipeline that uses the $lookup operator to do a left join from the parameters collection to the groups collection.

考虑运行以下管道:

db.parameters.aggregate([
    { "$unwind": "$groups" },
    {
        "$lookup": {
            "from": "groups",
            "localField": "groups",
            "foreignField": "_id",
            "as": "grp"
        }
    },
    { "$unwind": "$grp" }
])

样本输出

/* 1 */
{
    "_id" : ObjectId("56cac0cd0b5a1ffab1bd6c12"),
    "name" : "Speed",
    "groups" : "123",
    "grp" : {
        "_id" : "123",
        "name" : "Group01"
    }
}

/* 2 */
{
    "_id" : ObjectId("56cac0cd0b5a1ffab1bd6c12"),
    "name" : "Speed",
    "groups" : "234",
    "grp" : {
        "_id" : "234",
        "name" : "Group02"
    }
}

<小时>

如果您的 MongoDB 服务器版本不支持 $lookup 管道操作符，那么你需要执行如下两个查询:

---

If your MongoDB server version does not support the $lookup pipeline operator, then you'd need execute two queries as follows:

# get the group ids
ids = db.parameters.find_one({ "_id": ObjectId("56cac0cd0b5a1ffab1bd6c12") })["groups"]

# query the groups collection with the ids from previous query
db.groups.find({ "_id": { "$in": ids } })

将聚合查询中的字段名称与示例数据集中的字段名称匹配(在问题内)

matched the field name in the aggregation query to the field name in example dataset (within the question)

这篇关于MongoDB - 简单的子查询示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！