aggregate MongoDB array with nested documents

Question

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!

hhharsha36 · Accepted Answer · 2021-10-28 15:52:27Z

2

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

edited Oct 28, 2021 at 15:52

answered Oct 28, 2021 at 15:42

hhharsha36

3,3892 gold badges14 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ignareyesa Over a year ago

Thanks a lot! Trying to learn MongoDB

hhharsha36 Over a year ago

@ignareyesa no worries, we were all beginners at some point. You will get a grasp of it eventually. Keep practicing and raise your questions on StackOverflow. Hope my answer was what you are looking for.

ignareyesa Over a year ago

Absolutely! One more thing, would it be possible just to get the name and the price and not the rest of the element in the nested document?

hhharsha36 Over a year ago

I have updated the answer. Thanks for the upvote :)

Collectives™ on Stack Overflow

aggregate MongoDB array with nested documents

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related