Putting the results of 2 queries together in the returned data

Working with Data
#1

Hi everyone - got a bit of a question
Collection data -

    {
        "name": "name 1",
        "type": 50,
        "qty": 1
    },
    {
        "name": "name 2",
        "type": 52,
        "qty": 2
    },
    {
        "name": "name 3",
        "type": 50,
        "qty": 3
    },
    {
        "name": "name 4",
        "type": 52,
        "qty": 4
    },
    {
        "name": "name 5",
        "type": 50,
        "qty": 5
    }

and have 2 queries - sum and count

db.example.aggregate(
    {
        $match: {"type": 50}
    },
    {
        $group: {
            _id: null,
            total: { $sum: "$qty" },
            count: { $sum: 1 }
        }
    },
    {
        $project: {"_id": 0, total: 1, count: 1}
    }
)

and detail matching on some criteria

db.example.aggregate(
    {
        $match: {"type": 50}
    }
)

Is there any way of combining the 2 queries together so the sum and count and details appear together in the output? like so

{ "total" : 9, "count" : 3 }
 { "_id" : ObjectId("5eea8e09279c3208b5132694"), "name" : "name 1", "type" : 50, "qty" : 1 }
{ "_id" : ObjectId("5eea8e09279c3208b5132696"), "name" : "name 3", "type" : 50, "qty" : 3 }
{ "_id" : ObjectId("5eea8e09279c3208b5132698"), "name" : "name 5", "type" : 50, "qty" : 5 }

thanks
Steve

#2

Welcome to the community @Steve_Potts!

If you want to perform aggregrations for the same input documents across several dimensions, you can use a $facet stage containing multiple sub-pipelines for processing.

For example, converting your two aggregation pipelines into sumAndCount and documents facets:

db.example.aggregate([
    { $match: { "type": 50 } },
    { $facet: {
        "sumAndCount": [
            { $group: {
                 _id: null,
                total: { $sum: "$qty" },
                count: { $sum: 1 }
            }},
            { $project: {
                "_id": 0,
                total: 1,
                count: 1
            }}
        ],
        "documents" : [
            { $match: { "type": 50 } }
        ],
    }}
]).pretty()

Returns:

{
	"sumAndCount" : [
		{
			"total" : 9,
			"count" : 3
		}
	],
	"documents" : [
		{
			"_id" : ObjectId("5eeaa8a79ffc9476b2ccbd7b"),
			"name" : "name 1",
			"type" : 50,
			"qty" : 1
		},
		{
			"_id" : ObjectId("5eeaa8a79ffc9476b2ccbd7d"),
			"name" : "name 3",
			"type" : 50,
			"qty" : 3
		},
		{
			"_id" : ObjectId("5eeaa8a79ffc9476b2ccbd7f"),
			"name" : "name 5",
			"type" : 50,
			"qty" : 5
		}
	]
}

Since a facet sub-pipeline cannot be empty, I repeated the initial $match expression to return the documents.

Regards,
Stennie

#3

You can preserve your $match results by pushing every doc into array using $group stage:

{
  $group: {
    _id: null,
    total: { $sum: "$qty" },
    count: { $sum: 1 },
    list: { $push: '$$CURRENT' },
  }
},
#4

Thanks just the job - was trying to avoid putting details into an array but on reflection it is probably the better way of passing the data back to the application

#5

Thanks for the welcome - i had completely forgotten about $facet will look into that - comparatively new to Mongodb converting from microsoft SQL - will chose the other answer as the solution because my match criteria and the other pipeline commands are a bit complicated in my really world example - thanks again

#6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.