Suppose that I have a collection with documents like below

{
    "location" : "Tokyo",
    "attraction": {
        "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" : "Toronto",
    "attraction": {
        "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"
            }
        }
    }
},

How do I find documents that have matching field in the child object of Food? i.e. If I want to find document that has restaurant:"Fresh Tea"?

Currently what I have:

app.get(route, (req, res) => {
    var detail = {};
    if(req.query.location){
        detail['location'] = req.query.location.toUpperCase();
    }

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

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

Right now detail['attraction.food.food_0'] = req.query.restaurant is only able to find document that has matching food_0.restaurant, but I still can't find a way to make it check all child objects within "food".

CodePudding user response：

Using dynamic value as field name is generally considered as anti-pattern and should be avoided. Nevertheless, you can convert the object attraction.food to an array of k-v tuple and perform the search with your criteria. For your case, $anyElementTrue with $map will help with processing the array.

db.collection.aggregate([
  {
    "$addFields": {
      "test": {
        "$anyElementTrue": {
          "$map": {
            "input": {
              "$objectToArray": "$attraction.food"
            },
            "as": "t",
            "in": {
              $eq: [
                "$$t.v.restaurant",
                "Hibiki"
              ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      test: true
    }
  },
  {
    "$unset": "test"
  }
])

Here is the Mongo Playground for your reference.

CodePudding user response：

A possible aggregation pipeline

1. Add a temporary field using $addFields and $objectToArray which does something similar to javascript Object.entries()
2. Do the matching
3. Remove the added temporary field using $project 0

playground

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