Home > Net >  aggregate MongoDB array with nested documents
aggregate MongoDB array with nested documents

Time:10-29

I am trying to make a consultant on my MongoDB database:

What I want to do is to obtain some data that is integrated in a nested array and filter by one of this nested keys.

The document is the following:

[
  {
    "name": "PharmaMaria",
    "country": "Spain",
    "currency": "EUR",
    "medicines": [
      {
        "name": "Medicine 1",
        "type": "Suncream",
        "price": 32,
        
      },
      {
        "name": "Medicine 2",
        "type": "Suncream",
        "price": 5
      },
      {
        "name": "Medicine 3",
        "type": "Pills",
        "price": 7
      }
    ]
  }
]

And I want to get something like this filtering by medicines.type

values = [
  {
    "name": "Medicine 1",
    "price": 32
  },
  {
    "name": "Medicine 2",
    "price": 5
  }
]

Here is the playground I created https://mongoplayground.net/p/_riatO8PKVp

Thanks!

CodePudding user response:

You have to add a $project or $addFields stage and use $filter operator to apply the condition on each elements.

db.collection.aggregate([
  {
    "$match": {
      "country": "Spain",
      "medicines.type": "Suncream"
    },
    
  },
  {
    "$addFields": {  // <- To add a new key
      "medicines": {  // <- Replacing existing `medicines` key
        "$filter": {  // <- Apply condition on each array elements
          "input": "$medicines",
          "as": "elem",
          "cond": {  // <- Applying match condition inside this block
            "$eq": [
              "$$elem.type",
              "Suncream"
            ],
            
          }
        }
      }
    }
  },
  
])

To get Only specific keys from array, use $map

db.collection.aggregate([
  {
    "$match": {
      "country": "Spain",
      "medicines.type": "Suncream"
    },
    
  },
  {
    "$addFields": {
      "medicines": {
        "$map": {
          "input": {
            "$filter": {
              "input": "$medicines",
              "as": "elem",
              "cond": {
                "$eq": [
                  "$$elem.type",
                  "Suncream"
                ],
                
              }
            }
          },
          "as": "med",
          "in": {
            "name": "$$med.name",
            "price": "$$med.price",
            
          }
        },
        
      }
    }
  },
  
])

Above sample Mongo Execution

Mongo Playground Sample Execution

  • Related