数据库 - Mongodb多层嵌套数组如何精确查询？

Question

{
    "excelID": "10001",
    "excelName": "一年级学生信息",
    "sheetArrays": [
        {
            "sheetName": "一班",
            "studentInfos": [
                {
                    "name": "张三",
                    "sex": "男"
                },
                {
                    "name": "李四",
                    "sex": "男"
                }
            ]
        },
        {
            "sheetName": "二班",
            "studentInfos": [
                {
                    "name": "王五",
                    "sex": "男"
                },
                {
                    "name": "赵六",
                    "sex": "女"
                }
            ]
        }
    ]
}

我希望通过一条语句查出所有sex为男的人员的名称；

我还希望能通过一条语句查出name为王五对应的班级和他的基本信息，求朋友们为我答疑解惑，感激不尽。

Answer 1

What is the concept of a sentence? Does aggregation count as a statement?

db.test3.aggregate([
    {$match: {"sheetArrays.studentInfos.sex": '男'}},
    {$unwind: "$sheetArrays"},
    {$unwind: "$sheetArrays.studentInfos"},
    {$match: {"sheetArrays.studentInfos.sex": '男'}},
]);
db.test3.aggregate([
    {$match: {"sheetArrays.studentInfos.name": '王五'}},
    {$unwind: "$sheetArrays"},
    {$unwind: "$sheetArrays.studentInfos"},
    {$match: {"sheetArrays.studentInfos.name": '王五'}},
]);

Although it can be done, in fact such a data structure is not reasonable.
MongoDB model design is a big topic. If you are interested, you can read the data modeling chapter of the document carefully.
The basic principle is actually that there is no principle. This is much more troublesome than satisfying the relationship paradigm. There is no guideline telling you what to do. But basically, the data model you choose should take into account various scenarios of how to use it, how to update, how to insert, how to query... A model that can well meet the usage scenarios is a good model.
Go back to your model and see if there are any problems:

1. Take the WT engine as an example. Document-level locking. When anyone in any class makes changes, changes will be made to the document. That is to say, these operations are all mutually exclusive. The efficiency can be imagined. What a difference.

2. You have also seen the above query, which is neither elegant nor efficient. Not to mention updating, it’s troublesome just thinking about it.
   So this is not a good model. Although MongoDB advocates anti-paradigm, it should not be so extreme that it is anti-paradigm for the sake of anti-paradigm. Try a different model that is more convenient for you to use.