Mongodb多层嵌套数组如何更好的查询？

Question

现有数据如下

{
    "_id" : ObjectId("5992c90beeb45634df1c2be4"),
    "name" : "测试数据1",
    "status" : {
        "product" : [ 
            {
                "id" : ObjectId("59a37f7aef887a1d58b59f4f"),
                "status" : true
            }, 
            {
                "id" : ObjectId("59a37f7bef887a1d58b59f50"),
                "status" : false
            }, 
            {
                "id" : ObjectId("59a3801cef887a1d58b59f55"),
                "status" : false
            }
        ],
        "line":[]
    },
    "level" : [ 
        {
            "id" : ObjectId("59a37efeef887a1d58b59f43"),
            "level" : "A"
        }, 
        {
            "id" : ObjectId("59a37f03ef887a1d58b59f44"),
            "level" : "B"
        }, 
        {
            "id" : ObjectId("59a37f03ef887a1d58b59f45"),
            "level" : "C"
        }
    ]
},
{
    "_id" : ObjectId("599e7dc07780bbfa9bee42a9"),
    "name" : "test数据2",
    "status" : {
        "product" : [ 
            {
                "id" : ObjectId("59a37f7aef887a1d58b59f4f"),
                "status" : false
            }, 
            {
                "id" : ObjectId("59a37f7bef887a1d58b59f50"),
                "status" : false
            }, 
            {
                "id" : ObjectId("59a3801cef887a1d58b59f55"),
                "status" : false
            }
        ]
    },
    "location" : 6,
    "verify_state" : 0,
    "level" : [ 
        {
            "id" : ObjectId("59a37efeef887a1d58b59f43"),
            "level" : "A"
        }, 
        {
            "id" : ObjectId("59a37f03ef887a1d58b59f44"),
            "level" : "B"
        }, 
        {
            "id" : ObjectId("59a37f03ef887a1d58b59f45"),
            "level" : "C"
        }
    ]
}
......

我希望查找出status.product.id包含在 [ObjectId("59a37f7aef887a1d58b59f4f"),ObjectId("59a37f7bef887a1d58b59f50"),ObjectId("59a3801cef887a1d58b59f55")]数组里，同时对应的status.product.status为true的数据，查询语句应该怎么写呢？

Answer 1

你要的应该是指同一个元素同时满足status.product.id和status条件，这样的写法应该是$elemMatch。但是你要的到底是整个文档，还是数组中满足条件的元素？

// 找出满足条件的整个文档
db.test.find({$or: [
    {"status.product": {$elemMatch: {id: ObjectId("59a37f7aef887a1d58b59f4f"), status: true}}},
    {"status.product": {$elemMatch: {id: ObjectId("59a37f7bef887a1d58b59f50"), status: true}}},
    {"status.product": {$elemMatch: {id: ObjectId("59a3801cef887a1d58b59f55"), status: true}}}
]});

// 找出满足条件的元素
db.test.aggregate([
    {
        $match: {
            $or: [
                {"status.product": {$elemMatch: {id: ObjectId("59a37f7aef887a1d58b59f4f"), status: true}}},
                {"status.product": {$elemMatch: {id: ObjectId("59a37f7bef887a1d58b59f50"), status: true}}},
                {"status.product": {$elemMatch: {id: ObjectId("59a3801cef887a1d58b59f55"), status: true}}}
            ]
        }
    },
    {$unwind: "$status.product"},
    {
        $match: {
            "status.product.id": {
                $in: [
                    ObjectId("59a37f7aef887a1d58b59f4f"),
                    ObjectId("59a37f7bef887a1d58b59f50"),
                    ObjectId("59a3801cef887a1d58b59f55")
                ]
            },
            "status.product.status": true
        }
    }
]);

几点说明：

1. 出于效率考虑，应该为集合加上索引：

   db.test.createIndex({"status.product.id": 1, "status.product.status": 1});

2. $elemMatch的用法请查阅文档自己理解。

3. 查询2的第一个$match其实就是为了利用到上面的索引来加速查询，因为$unwind之后就无法再利用索引了。