代码之家 › 专栏 › 技术社区 › Green Cell

如何添加包含匹配文档的嵌入字段

pymongo aggregation-framework mongodb

0

Green Cell · 技术社区 · 5 年前

我正在使用Python和pymongo从数据库中进行查询。

我有3个不同的系列:

第一:

# Projects collection
{
    "_id": "A",
},
{
    "_id": "B",
},
{
    "_id": "C"
},
..

第二:

# Episodes collection
{
    "_id": "A/Episode01",
    "project": "A",
    "name": "Episode01"
},
{
    "_id": "A/Episode02",
    "project": "A",
    "name": "Episode02"
},
{
    "_id": "B/Episode01",
    "project": "B",
    "name": "Episode01"
},
..

第三:

# Sequences collection
{
    "_id": "A/Episode01/Sequence01",
    "project": "A",
    "episode": "Episode01",
    "name": "Sequence01"
},
{
    "_id": "A/Episode02/Sequence02",
    "project": "A",
    "episode": "Episode02",
    "name": "Sequence02"
},
{
    "_id": "B/Episode01/Sequence01",
    "project": "B",
    "episode": "Episode01",
    "name": "Sequence01"
},
..

我想使用聚合来查询项目 A 得到所有相应的剧集和序列如下:

{
    "_id": "A",
    "episodes": 
    [
        {
            "_id": "A/Episode01",
            "project": "A",
            "name": "Episode01",
            "sequences": 
            [
                {
                    "_id": "A/Episode01/Sequence01",
                    "project": "A",
                    "episode": "Episode01",
                    "name": "Sequence01"
                },
            ]
        },
        {
            "_id": "A/Episode02",
            "project": "A",
            "name": "Episode02",
            "sequences":
            [
                {
                    "_id": "A/Episode02/Sequence02",
                    "project": "A",
                    "episode": "Episode02",
                    "name": "Sequence02"
                },
            ]
        },
    ]
}

我可以尽可能地获得正确的剧集,但我不确定如何为任何匹配的序列添加嵌入字段。有可能在一个管道查询中完成这一切吗?

现在我的问题是这样的:

[
    {"$match": {
        "_id": "A"}
    },
    {"$lookup": {
        "from": "episodes",
        "localField": "_id",
        "foreignField": "project",
        "as": "episodes"}
    },
    {"$group": {
        "_id": {
            "_id": "$_id",
            "episodes": "$episodes"}
    }}
]

0 回复 | 直到 5 年前

1

2

varman 5 年前

你可以像下面这样做

使用 $match 匹配文档
使用 uncorrelated queries 加入两个系列。但正如你所写,正常的加入也是可能的。当我们遇到一些复杂的情况时,这会更容易。

Mongo脚本如下所示

[
  {
    "$match": {
      "_id": "A"
    }
  },
  {
    $lookup: {
      from: "Episodes",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$project",
                "$$id"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "Sequences",
            let: {
              epi: "$name"
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $eq: [
                      "$episode",
                      "$$epi"
                    ]
                  }
                }
              }
            ],
            as: "sequences"
          }
        }
      ],
      as: "episodes"
    }
  }
]

工作 Mongo playground

更新01

使用标准查找

[
  {
    "$match": {
      "_id": "A"
    }
  },
  {
    "$lookup": {
      "from": "Episodes",
      "localField": "_id",
      "foreignField": "project",
      "as": "episodes"
    }
  },
  {
    $unwind: "$episodes"
  },
  {
    "$lookup": {
      "from": "Sequences",
      "localField": "episodes.name",
      "foreignField": "episode",
      "as": "episodes.sequences"
    }
  },
  {
    $group: {
      _id: "$episodes._id",
      episodes: {
        $addToSet: "$episodes"
      }
    }
  }
]

工作 Mongo playground