logo标签列表

在MongoDB集合中，只检索对象数组中查询的元素

mongodbmongodb-queryaggregation-frameworkprojection
481

假设您在我的集合中有以下文档：

{  
   "_id":ObjectId("562e7c594c12942f08fe4192"),
   "shapes":[  
      {  
         "shape":"square",
         "color":"blue"
      },
      {  
         "shape":"circle",
         "color":"red"
      }
   ]
},
{  
   "_id":ObjectId("562e7c594c12942f08fe4193"),
   "shapes":[  
      {  
         "shape":"square",
         "color":"black"
      },
      {  
         "shape":"circle",
         "color":"green"
      }
   ]
}

执行查询：

db.test.find({"shapes.color": "red"}, {"shapes.color": 1})

或者

db.test.find({shapes: {"$elemMatch": {color: "red"}}}, {"shapes.color": 1})
返回匹配的文档（文档1），但始终包含shapes中的所有数组项。
{ "shapes": 
  [
    {"shape": "square", "color": "blue"},
    {"shape": "circle", "color": "red"}
  ] 
}

然而，我想要获取包含color=red的数组中的文档(文档1)：

{ "shapes": 
  [
    {"shape": "circle", "color": "red"}
  ] 
}

我该如何做这件事？

20个回答
5
同样，您也可以找到多个。
db.getCollection('localData').aggregate([
    // Get just the docs that contain a shapes element where color is 'red'
  {$match: {'shapes.color': {$in : ['red','yellow'] } }},
  {$project: {
     shapes: {$filter: {
        input: '$shapes',
        as: 'shape',
        cond: {$in: ['$$shape.color', ['red', 'yellow']]}
     }}
  }}
])
2
这个答案确实是首选的4.x方式：使用$match来减少空间，然后使用$filter来保留所需内容，并覆盖输入字段（使用shapes字段的$filter输出来在shapes上进行$project）。风格注意事项：最好不要将字段名称用作as参数，因为这可能会在以后与$$shape和$shape混淆。我更喜欢将zz作为as字段，因为它真的很突出。 - Buzz Moschetti
2
db.test.find( {"shapes.color": "red"}, {_id: 0})
4
欢迎来到 Stack Overflow！感谢您提供的代码片段，它可能为一些有限的、即时的帮助提供了一些可能。一个适当的解释会大大提高它的 长期价值，通过描述为什么这是一个好的解决方案，并使它对未来遇到类似问题的读者更有用。请编辑您的答案添加一些解释，包括您所做的假设。 - sepehr
2
虽然这个问题是9.6年前提出的，但它对许多人来说都非常有帮助，我也是其中之一。感谢大家提出的所有问题、提示和答案。从这里的一个答案中得到启发...我发现以下方法也可以用于在父文档中投影其他字段。这可能对某些人有所帮助。
对于下面的文档，需要找出员工(emp #7839)是否已经为2020年设置了他的请假历史记录。请假历史记录作为嵌入在父Employee文档中的文档实现。
db.employees.find( {"leave_history.calendar_year": 2020}, 
    {leave_history: {$elemMatch: {calendar_year: 2020}},empno:true,ename:true}).pretty()


{
        "_id" : ObjectId("5e907ad23997181dde06e8fc"),
        "empno" : 7839,
        "ename" : "KING",
        "mgrno" : 0,
        "hiredate" : "1990-05-09",
        "sal" : 100000,
        "deptno" : {
                "_id" : ObjectId("5e9065f53997181dde06e8f8")
        },
        "username" : "none",
        "password" : "none",
        "is_admin" : "N",
        "is_approver" : "Y",
        "is_manager" : "Y",
        "user_role" : "AP",
        "admin_approval_received" : "Y",
        "active" : "Y",
        "created_date" : "2020-04-10",
        "updated_date" : "2020-04-10",
        "application_usage_log" : [
                {
                        "logged_in_as" : "AP",
                        "log_in_date" : "2020-04-10"
                },
                {
                        "logged_in_as" : "EM",
                        "log_in_date" : ISODate("2020-04-16T07:28:11.959Z")
                }
        ],
        "leave_history" : [
                {
                        "calendar_year" : 2020,
                        "pl_used" : 0,
                        "cl_used" : 0,
                        "sl_used" : 0
                },
                {
                        "calendar_year" : 2021,
                        "pl_used" : 0,
                        "cl_used" : 0,
                        "sl_used" : 0
                }
        ]
}
2

使用聚合函数和$project来获取文档中特定的对象字段

db.getCollection('geolocations').aggregate([ { $project : { geolocation : 1} } ])

结果：

{
    "_id" : ObjectId("5e3ee15968879c0d5942464b"),
    "geolocation" : [ 
        {
            "_id" : ObjectId("5e3ee3ee68879c0d5942465e"),
            "latitude" : 12.9718313,
            "longitude" : 77.593551,
            "country" : "India",
            "city" : "Chennai",
            "zipcode" : "560001",
            "streetName" : "Sidney Road",
            "countryCode" : "in",
            "ip" : "116.75.115.248",
            "date" : ISODate("2020-02-08T16:38:06.584Z")
        }
    ]
}
1

如果您想同时执行过滤、设置和查找，则可以这样做。

let post = await Post.findOneAndUpdate(
          {
            _id: req.params.id,
            tasks: {
              $elemMatch: {
                id: req.params.jobId,
                date,
              },
            },
          },
          {
            $set: {
              'jobs.$[i].performer': performer,
              'jobs.$[i].status': status,
              'jobs.$[i].type': type,
            },
          },
          {
            arrayFilters: [
              {
                'i.id': req.params.jobId,
              },
            ],
            new: true,
          }
        );
1

这个答案并没有完全回答问题，但它是相关的，我写下来是因为有人决定关闭另一个问题并将其标记为重复（这不是重复的）。

在我的情况下，我只想过滤数组元素，但仍然返回数组的完整元素。所有先前的答案（包括问题中给出的解决方案）在应用到我的特定情况时都让我头疼，因为：

  • 我需要我的解决方案能够返回多个子数组元素的结果。
  • 使用$unwind+$match+$group会导致失去根文档而没有匹配数组元素，在我的情况下，我不想这样做，因为实际上我只想过滤掉不需要的元素。
  • 使用$project>$filter会导致丢失其他字段或根文档，或者强制我在投影中指定所有字段，这是不可取的。

所以最后我用这样的$addFields>$filter解决了所有这些问题：

db.test.aggregate([
    { $match: { 'shapes.color': 'red' } },
    { $addFields: { 'shapes': { $filter: {
      input: '$shapes',
      as: 'shape',
      cond: { $eq: ['$$shape.color', 'red'] }
    } } } },
])

说明：

  1. 首先匹配具有红色形状的文档。
  2. 对于这些文档，添加一个名为shapes的字段，该字段将替换原始字段，名称相同。
  3. 为了计算shapes的新值，$filter原始$shapes数组的元素，临时将每个数组元素命名为shape，以便稍后我们可以检查$$shape.color是否为红色。
  4. 现在，新的shapes数组仅包含所需的元素。
1

对于新版本的MongoDB，有些不同。

对于db.collection.find，您可以使用find的第二个参数，其中键为projection

db.collection.find({}, {projection: {name: 1, email: 0}});

您也可以使用.project()方法。
但是，它不是MongoDB的本地方法，而是由大多数MongoDB驱动程序（如Mongoose、MongoDB Node.js驱动程序等）提供的方法。

db.collection.find({}).project({name: 1, email: 0});

如果你想使用findOne，它与find是相同的

db.collection.findOne({}, {projection: {name: 1, email: 0}});

但是findOne没有.project()方法。

0

更多细节请参考 =

MongoDB官方文档

suppose you have document like this (you can have multiple document too) - 

{
  "_id": {
    "$oid": "63b5cfbfbcc3196a2a23c44b"
  },
  "results": [
    {
      "yearOfRelease": "2022",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/d/d4/The_Kashmir_Files_poster.jpg",
      "title": "The Kashmir Files",
      "overview": "Krishna endeavours to uncover the reason behind his parents' brutal killings in Kashmir. He is shocked to uncover a web of lies and conspiracies in connection with the massive genocide.",
      "originalLanguage": "hi",
      "imdbRating": "8.3",
      "isbookMark": null,
      "originCountry": "india",
      "productionHouse": [
        "Zee Studios"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44c"
      }
    },
    {
      "yearOfRelease": "2022",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/a/a9/Black_Adam_%28film%29_poster.jpg",
      "title": "Black Adam",
      "overview": "In ancient Kahndaq, Teth Adam was bestowed the almighty powers of the gods. After using these powers for vengeance, he was imprisoned, becoming Black Adam. Nearly 5,000 years have passed, and Black Adam has gone from man to myth to legend. Now free, his unique form of justice, born out of rage, is challenged by modern-day heroes who form the Justice Society: Hawkman, Dr. Fate, Atom Smasher and Cyclone",
      "originalLanguage": "en",
      "imdbRating": "8.3",
      "isbookMark": null,
      "originCountry": "United States of America",
      "productionHouse": [
        "DC Comics"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44d"
      }
    },
    {
      "yearOfRelease": "2022",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/0/09/The_Sea_Beast_film_poster.png",
      "title": "The Sea Beast",
      "overview": "A young girl stows away on the ship of a legendary sea monster hunter, turning his life upside down as they venture into uncharted waters.",
      "originalLanguage": "en",
      "imdbRating": "7.1",
      "isbookMark": null,
      "originCountry": "United States Canada",
      "productionHouse": [
        "Netflix Animation"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44e"
      }
    },
    {
      "yearOfRelease": "2021",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/7/7d/Hum_Do_Hamare_Do_poster.jpg",
      "title": "Hum Do Hamare Do",
      "overview": "Dhruv, who grew up an orphan, is in love with a woman who wishes to marry someone with a family. In order to fulfil his lover's wish, he hires two older individuals to pose as his parents.",
      "originalLanguage": "hi",
      "imdbRating": "6.0",
      "isbookMark": null,
      "originCountry": "india",
      "productionHouse": [
        "Maddock Films"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c44f"
      }
    },
    {
      "yearOfRelease": "2021",
      "imagePath": "https://upload.wikimedia.org/wikipedia/en/7/74/Shang-Chi_and_the_Legend_of_the_Ten_Rings_poster.jpeg",
      "title": "Shang-Chi and the Legend of the Ten Rings",
      "overview": "Shang-Chi, a martial artist, lives a quiet life after he leaves his father and the shadowy Ten Rings organisation behind. Years later, he is forced to confront his past when the Ten Rings attack him.",
      "originalLanguage": "en",
      "imdbRating": "7.4",
      "isbookMark": null,
      "originCountry": "United States of America",
      "productionHouse": [
        "Marvel Entertainment"
      ],
      "_id": {
        "$oid": "63b5cfbfbcc3196a2a23c450"
      }
    }
  ],
  "__v": 0
}



=======


mongo db query by aggregate command - 


mongomodels.movieMainPageSchema.aggregate(
        [
            {
               $project: {
                _id:0,  // to supress id
                results: {
                     $filter: {
                        input: "$results",
                        as: "result",
                        cond: { $eq: [ "$$result.yearOfRelease", "2022" ] }
                     }
                  }
               }
            }
         ]

    )

0
如果你想返回第一个匹配的shapes，find()就可以了（详见这里），但如果你想返回原始文档中每个匹配的shapes，可以使用以下aggregate：
db.collection.aggregate([
  {
    $set: {
      shapes: {
        $filter: {
          input: "$shapes",
          as: "shape",
          cond: {$eq: ["$$shape.color", "red"]}
        }
      }
    }
  },
  {
    $match: {
      $nor: [
        {shapes: {$exists: false}},
        {shapes: {$size: 0}}
      ]
    }
  }
])

在Mongo Playground中玩一下{{link1：in mongo playground}}。这是直接过滤shapes属性并保留其他属性（而不是使用$project删除其他属性，并需要额外的步骤来获取文档）。
附加的$match是可选的，它会删除空shapes数组的文档。
0
    db.collection.aggregate([
      {
        "$unwind": "$shapes"
      },
      {
        "$match": {
          "$and": [
            {
              "shapes.color": "red"
            },
            {
              "shapes.shape": "circle"
            }
          ]
        }
      },
      //remove objectid
      {
        "$project": {
          _id: 0
        }
      }
    ])
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接