CODEWITHSUNDEEP
mongodb
Lakshya Bansal
Lakshya Bansal

Reputation: 113

How to group by one matching element in a subdocument - Mongodb

Suppose I have the following collection. 

[
  {
    "items": {
      "item": [
        {
          "@pid": "131",
          "text": "Apple"
        },
        {
          "@pid": "61",
          "text": "Mango"
        },
        {
          "@pid": "92",
          "text": "cherry"
        },
        {
          "@pid": "27",
          "text": "grape"
        },
        {
          "@pid": "34",
          "text": "dragonfruit"
        }
      ]
    },
    "type": "A"
  },
  {
    "items": {
      "item": [
        {
          "@pid": "131",
          "text": "Apple"
        },
        {
          "@pid": "27",
          "text": "grape"
        },
        {
          "@pid": "34",
          "text": "dragonfruit"
        }
      ]
    },
    "type": "B"
  },
  {
    "items": {
      "item": [
        {
          "@pid": "131",
          "text": "Apple"
        }
      ]
    },
    "type": "A"
  }
]

I want to get the type in which apple or mango is sold, group by item name. For the above collection, the output would be : 

{
    "_id": "Apple",
    "items" : [
        "A",
        "B",
        "A"
    ]
},
{
    "_id": "Mango",
    "items" : [
        "A"
    ]
}

I tried the following query but it return nothing : 

db.collection.aggregate([

     {
         $match : {
             'items.item.text' : {$regex : 'Apple|Mango'}
         }

     },
     {
        $project : {
            type : "$type"
        }
     },
     {
         $group : {
             _id : '$items.item',
                types : {$push : '$type'}

         }
     }


 ])

I think that even if this works, it's going to group by the entire 'items.item'. Where am I going wrong?

P.S. : I don't have the liberty to change the format of the document

Thanks a lot in advance.

Upvotes: 0

Views: 18

Answers (1)

ngShravil.py
ngShravil.py

Reputation: 5048

You were on the right direction. You need to use $unwind operator and you don't need $project stage in your aggregation. The below query will be helpful:

db.collection.aggregate([
  {
    $unwind: "$items.item"
  },
  {
    $match: {
      "items.item.text": {
        $regex: "Apple|Mango"
      }
    }
  },
  {
    $group: {
      _id: "$items.item.text",
      type: {
        $push: "$type"
      }
    }
  }
])

MongoPlayGroundLink

Upvotes: 1

Related Questions