Group by using an Attribute Pattern

Working with Data
, ,
1

Hi everyone!

I´m wondering about the best way to aggregate a collection using the attribute pattern.

My documents looks this way:

{
	"_id" : "5fdb9469aa7d50693d33f522",
	"typeName" : "POApproval",
	"milestones" : [
		{
			"name" : "rejected",
			"lastReached" : null
		},
		{
			"name" : "sent",
			"lastReached" : null
		},
		{
			"name" : "cancelled",
			"lastReached" : null
		},
		{
			"name" : "totalApproved",
			"lastReached" : null
		},
		{
			"name" : "supplierApproved",
			"lastReached" : ISODate("2021-02-09T14:35:35.941Z")
		}
	],
	"tags" : [
		{
			"name" : "supplierCode",
			"value" : "9"
		},
		{
			"name" : "costCenter",
			"value" : null
		}
	],
	"metrics" : [
		{
			"name" : "total",
			"value" : "9"
		}
	]
}

If I need to group by supplierCode, I´m executing a query like this:

db.Contexts.aggregate([
    { $project: { _id: 0, tags: "$tags" } },
    { $unwind: "$tags" },
    { $match: { $expr: { $eq: ["$tags.name", "supplierCode"] } } },
    { $group: { _id: { tag: "$tags.value", count: { $sum: 1 } } }
])

Is there a better way to improve it and avoid a collection scan?

Thanks in advance.

2

Hello @faramos,

Here is, I think, is a better way to aggregate the query.

db.collection.aggregate([
  { 
    $match: { "tags.name": "supplierCode" } 
  },
  { 
    $unwind: "$tags" 
  },
  { 
    $group: { 
        _id: { 
            suppliers: { 
                $cond: [ { $eq: [ "$tags.name", "supplierCode" ] }, "$tags.value",  null ] 
            }
        },
        count: { $sum: 1 }
    }
  },
  { 
    $match: { "_id.suppliers": { $ne: null } } 
  },
])

Now, you can define an index on the "tags.name" array field (a Multikey Index) and the query will benefit from it.