MongoDB匹配包含数组字段的文档，其中包含与查询匹配的所有元素-Java 学习之路

来自$elementMatch的MongoDB文档：

$ elemMatch运算符匹配包含数组字段的文档，其中至少有一个元素匹配所有指定的查询条件 .

But how can I match documents that contain an array field with ALL elements that match the query?

例如，我有这样的文件：

{
    "_id": ObjectId("55c99649b8b5fc5b0a2f1c83"),
    "sku": "ed-39211",
    "created_at": ISODate("2015-08-11T06:29:29.139+0000"),
    "formats": [{
        "name": "thefile",
        "_id": ObjectId("55c99649f2e2d6353348ec9c"),
        "prices": [{
            "price": 4.49,
            "currency": "GBP",
            "territory": "GB",
            "_id": ObjectId("55c99649f2e2d6353348ec9f")
        }, {
            "price": 6.99,
            "currency": "USD",
            "territory": "US",
            "_id": ObjectId("55c99649f2e2d6353348ec9e")
        }, {
            "price": 6.99,
            "currency": "CHF",
            "territory": "CH",
            "_id": ObjectId("55c99649f2e2d6353348ec9d")
        }]
    }]
}

我需要匹配 all formats.prices.price > 5的所有文档

如果我使用以下查询：

{ 'formats.prices': { $elemMatch: { price: { $gte: 5 } } } }

该文件将匹配，因为至少有一个价格> 5

我也试过这个，但它似乎不起作用：

{ 'formats.prices': { $all: { $elemMatch: {price: { $gte: 0.98 } } } } }

有没有办法排除该文件，查看所有价格至少不是一个？

2 回答

2
找到了！这很简单，只需使用 $not 运算符并检查相反（<5）：
```
{ 'formats.prices': { $not: { $elemMatch: {price: { $lt: 5 } } } } }
```
回复于 2024-04-19T18:57:34+08:00

您可以使用Aggegation或MAP REDUCE来实现它：

First solution is using Map-Reduce ：

我创建了一个名为“format”的集合并插入到数据下面：

{
        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c83"),
        "sku" : "ed-39211",
        "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
        "formats" : [
                {
                        "name" : "thefile",
                        "_id" : ObjectId("55c99649f2e2d6353348ec9c"),
                        "prices" : [
                                {
                                        "price" : 4.49,
                                        "currency" : "GBP",
                                        "territory" : "GB",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "USD",
                                        "territory" : "US",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "CHF",
                                        "territory" : "CH",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                }
                        ]
                }
        ]
}
{
        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
        "sku" : "ed-39211",
        "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
        "formats" : [
                {
                        "name" : "thefile",
                        "_id" : ObjectId("55c99649f2e2d6353348ec9a"),
                        "prices" : [
                                {
                                        "price" : 5.49,
                                        "currency" : "GBP",
                                        "territory" : "GB",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "USD",
                                        "territory" : "US",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "CHF",
                                        "territory" : "CH",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                }
                        ]
                }
        ]
}

Map_reduce :

db.format.mapReduce(
    function()
            {
            var doc  = {"_id" : this._id, "sku" : this.sku, "created_at" : this.created_at, "formats" : this.formats};
            var prices;
            var flag = 0;
            for ( var i = 0 ; i < doc.formats.length; i++)
            {
                prices = doc.formats[i].prices
                for ( var j =0 ; j < prices.length; j++)
                {
                    if( prices[j].price < 5)
                    {
                        flag = 1;
                        break;
                    }
                }
                if( flag == 1)
                doc.formats.splice(i,1);
            }
                if( doc.formats.length > 0 )
                emit( this._id, doc);
            },
    function(){},
    { "out": { "inline": 1 } }
)

输出：

{
        "results" : [
                {
                        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
                        "value" : {
                                "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
                                "sku" : "ed-39211",
                                "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
                                "formats" : [
                                        {
                                                "name" : "thefile",
                                                "_id" : ObjectId("55c99649f2e2d6353348ec9a"),
                                                "prices" : [
                                                        {
                                                                "price" : 5.49,
                                                                "currency" : "GBP",
                                                                "territory" : "GB",
                                                                "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                                        },
                                                        {
                                                                "price" : 6.99,
                                                                "currency" : "USD",
                                                                "territory" : "US",
                                                                "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                                        },
                                                        {
                                                                "price" : 6.99,
                                                                "currency" : "CHF",
                                                                "territory" : "CH",
                                                                "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                                        }
                                                ]
                                        }
                                ]
                        }

Second Solution using Aggregation ：

使用聚合运算符$ unwind和$ size我们可以使用以下查询获得所需的结果：

在"Formats"和"Formats.prices"的$unwind之后，"Formats.prices"的大小被采用，然后"prices"上的$match完成，并且再次为"Formats.prices"计算新大小 .

如果大小相同，则“格式”字段中的所有“价格”都大于5，并且将投影文档 .

db.format.aggregate([
{ $unwind: "$formats" },
{ $project : { _id : 1, sku : 1, created_at : 1, formats : 1, "size" : { $size : "$formats.prices" } } },
{ $unwind: "$formats.prices" },
{ $match: { "formats.prices.price" : { $gt:5 } } },
{ $group: { _id: { "name" : "$formats.name" , "_id" : "$formats._id", "id" : "$_id" }, prices : { $push: "$formats.prices" } , sku: { $first: "$sku" }, created_at : { $first: "$created_at" }, oldsize : { $first: "$size" } } },
{ $project: { _id : 1, prices : 1, sku : 1, created_at : 1, oldsize : 1, newsize : {$size: "$prices" } } },
{ $project: { _id : 1, prices : 1, sku : 1, created_at : 1, cmp_value: { $cmp: ["$oldsize", "$newsize"] } } },
{ $match: { cmp_value:{ $eq:0 } } },
{ $group : { _id : "$_id.id" , sku: { $first: "$sku" }, created_at : { $first: "$created_at" }, formats : { $push: { name : "$_id.name", "_id" : "$_id._id", prices: "$prices" } }  } }
                  ]).pretty()

输出：

{
        "_id" : ObjectId("55c99649b8b5fc5b0a2f1c84"),
        "sku" : "ed-39211",
        "created_at" : ISODate("2015-08-11T06:29:29.139Z"),
        "formats" : [
                {
                        "name" : "thefile",
                        "_id" : ObjectId("55c99649f2e2d6353348ec9a"),
                        "prices" : [
                                {
                                        "price" : 5.49,
                                        "currency" : "GBP",
                                        "territory" : "GB",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9f")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "USD",
                                        "territory" : "US",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9e")
                                },
                                {
                                        "price" : 6.99,
                                        "currency" : "CHF",
                                        "territory" : "CH",
                                        "_id" : ObjectId("55c99649f2e2d6353348ec9d")
                                }
                        ]
                }
        ]
}

回复于 2024-04-19T18:57:34+08:00

MongoDB匹配包含数组字段的文档，其中包含与查询匹配的所有元素

2 回答

相关问题