Question 117120 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to find documents with child object that has matching value?

Suppose that I have a collection with documents like below

{
    "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"
            }
        }
    }
},

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.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

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".

Updated with more info:
User has the option to enter multiple search categories, and I want to combine all the search requests into "detail" and find all matching results. I.e. If user looks for transportation="Subway" and food="Bubble tea", then both Taipei and Toronto should come up as result.

Tags:

2

Answers


  1. 0

    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": {
              $or: [
                {
                  $eq: [
                    "$$t.v.transportation",
                    "Subway"
                  ]
                },
                {
                  $eq: [
                    "$$t.v.name",
                    "Bubble tea"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      test: true
    }
  },
  {
    "$unset": "test"
  }
])

    Here is the Mongo Playground for your reference.

    Login or Signup to reply.
  2. 0

    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
    },
    
  },
  
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search