如何找到具有匹配值的子对象的文档？

Question

假设我有一个包含如下文档的集合

{
    "location" : "Tokyo",
    "region" : "Asia",
    "attraction": {
        "transportation" : "Subway",
        "food" : {
            "food_0" : {
                "name" : "Sushi",
                "price" : 100,
                "restaurant" : "Ookinza"
            },
            "food_1" : {
                "name" : "Sashimi",
                "price" : 200,
                "restaurant" : "Hibiki"
            },
            "food_2" : {
                "name" : "N/A",
                "price" : "N/A",
                "restaurant" : "N/A"
            }
        }
    }
},
{
    "location" : "Taipei",
    "region" : "Asia",
    "attraction": {
        "transportation" : "Subway",
        "food" : {
            "food_0" : {
                "name" : "Bubble tea",
                "price" : 50,
                "restaurant" : "The Alley"
            },
            "food_1" : {
                "name" : "Oyster cake",
                "price" : 100,
                "restaurant" : "Night market"
            },
            "food_2" : {
                "name" : "N/A",
                "price" : "N/A",
                "restaurant" : "N/A"
            }
        }
    }
},
{
    "location" : "Toronto",
    "region" : "North America",
    "attraction": {
        "transportation" : "Uber",
        "food" : {
            "food_0" : {
                "name" : "Raman",
                "price" : 300,
                "restaurant" : "Kinto"
            },
            "food_1" : {
                "name" : "Bubble tea",
                "price" : 200,
                "restaurant" : "Fresh Fruit"
            },
            "food_2" : {
                "name" : "N/A",
                "price" : "N/A",
                "restaurant" : "N/A"
            }
        }
    }
},

如何在“食物”的子对象中找到具有匹配字段的文档？也就是说，如果我想找到有餐厅的文件：“新鲜茶”？

我现在拥有的是：

app.get(route, (req, res) => {
    var detail = {};
    if(req.query.location){
        detail['location'] = req.query.location.toUpperCase();
    }
    if(req.query.region){
        detail['region'] = req.query.region.toUpperCase();
    }
    if(req.query.transportation){
        detail['attraction.transportation'] = new RegExp(req.query.transportation.split(","), "i"),
    }

    if(req.query.restaurant){
        detail['attraction.food.food_0'] = req.query.restaurant;
    }

    db.collection(config.dbCollections.foodDB)
        .aggregate([
            $match: detail,
        },
        {
            $lookup: {
       ... // code continues

现在，detail['attraction.food.food_0'] = req.query.restaurant只能找到具有匹配food_0.restaurant的文档，但我仍然找不到一种方法让它检查"food“中的所有子对象。

更新了更多信息：

用户可以选择输入多个搜索类别，我希望将所有搜索请求合并到“细节”中，并找到所有匹配的结果。也就是说，如果用户寻找transportation="Subway"和food="Bubble tea"，那么台北和多伦多都会因此而出现。

Answer 1

使用动态值作为字段名通常被认为是反模式的，应该避免.不过，您可以将对象attraction.food转换为k-v元组数组，并使用您的条件执行搜索。对于您的情况，$anyElementTrue和$map将帮助处理数组。

db.collection.aggregate([
  {
    "$addFields": {
      "test": {
        "$anyElementTrue": {
          "$map": {
            "input": {
              "$objectToArray": "$attraction.food"
            },
            "as": "t",
            "in": {
              $or: [
                {
                  $eq: [
                    "$$t.v.transportation",
                    "Subway"
                  ]
                },
                {
                  $eq: [
                    "$$t.v.name",
                    "Bubble tea"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      test: true
    }
  },
  {
    "$unset": "test"
  }
])

这是供您参考的蒙戈游乐场。

Answer 2

一个可能的聚合管道

1. 使用$addFields和$objectToArray添加一个临时字段，该字段执行类似于javascript Object.entries()的操作
2. 做匹配
3. 使用$project 0删除添加的临时字段

游乐场

db.collection.aggregate([
  {
    "$addFields": {
      "foodArray": {
        "$objectToArray": "$attraction.food"
      },
      
    },
    
  },
  {
    "$match": {
      "foodArray.v.restaurant": "Fresh Fruit"
    }
  },
  {
    "$project": {
      "foodArray": 0
    },
    
  },
  
])