[SERVER-28954] Multiple out stages in aggregate Created: 25/Apr/17  Updated: 05/Dec/22

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Paul Reed Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-8850 $tee command for aggregation framework Closed
is duplicated by SERVER-51889 Introduce new stage to peek / debug a... Closed
Related
is related to SERVER-51889 Introduce new stage to peek / debug a... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

I would like to be able to use the $out pipeline embedded into the aggregation in multiple locations to snapshot the current output.

Example

[stock] is made up like:
{ _id:1, type:'widgets', material:'metal', 	name:'item1', count:5 }
{ _id:2, type:'widgets', material:'metal', 	name:'item2', count:3 }
{ _id:3, type:'widgets', material:'wood', 	name:'item3', count:2 }
{ _id:4, type:'widgets', material:'wood', 	name:'item4', count:5 }
{ _id:5, type:'widgets', material:'plastic',name:'item5', count:6 }
{ _id:6, type:'casing', material:'metal', 	name:'item6', count:0 }
{ _id:7, type:'casing', material:'wood', 	name:'item7', count:1 }
{ _id:8, type:'casing', material:'plastic', name:'item8', count:9 }
{ _id:9, type:'casing', material:'plastic', name:'item9', count:2 }
 
db.stock.aggregate([
	{ $group:{ _id: { k1:'$type', k2:'$material' }, totalcount:{$sum:'$count'} } },
	{ $out:'group_type_material' },
	{ $group:{ _id: '$_id.k1', totalcount:{$sum:'$totalcount'} } },
	{ $out:'group_type' },
	{ $group:{ _id: null, totalcount:{$sum:'$totalcount'} } },
	{ $out:'group_all' },
])

which would result in collections of

[group_type_material]:
 
{ _id: { k1:'widgets', k2:'metal'}, totalcount:8 }
{ _id: { k1:'widgets', k2:'wood'}, totalcount:7 }
{ _id: { k1:'widgets', k2:'plastic'}, totalcount:6 }
{ _id: { k1:'casing',  k2:'metal'}, totalcount:0 }
{ _id: { k1:'casing',  k2:'wood'}, totalcount:1 }
{ _id: { k1:'casing',  k2:'plastic'}, totalcount:11 }
 
 
[group_type]:
 
{ _id: 'widgets', totalcount:21 }
{ _id: 'casing', totalcount:12 }
 
 
[group_all]:
 
{ _id: null, totalcount:33 }

This would make this aggregation truly powerful in my opinion.



 Comments   
Comment by Asya Kamsky [ 27/Dec/17 ]

Note that something similar can be done with $facet:

db.stock.aggregate([{$facet:{
   group_type_material:[{ $group:{ _id: { k1:'$type', k2:'$material' }, totalcount:{$sum:'$count'} } }], 
   group_type:[{ $group:{ _id: '$type', totalcount:{$sum:'$count'} } }], 
   group: [{ $group:{ _id: null, totalcount:{$sum:'$count'} } } ]
}}] )
{
	"group_type_material" : [
		{
			"_id" : {
				"k1" : "casing",
				"k2" : "plastic"
			},
			"totalcount" : 11
		},
		{
			"_id" : {
				"k1" : "casing",
				"k2" : "metal"
			},
			"totalcount" : 0
		},
		{
			"_id" : {
				"k1" : "widgets",
				"k2" : "plastic"
			},
			"totalcount" : 6
		},
		{
			"_id" : {
				"k1" : "widgets",
				"k2" : "wood"
			},
			"totalcount" : 7
		},
		{
			"_id" : {
				"k1" : "casing",
				"k2" : "wood"
			},
			"totalcount" : 1
		},
		{
			"_id" : {
				"k1" : "widgets",
				"k2" : "metal"
			},
			"totalcount" : 8
		}
	],
	"group_type" : [
		{
			"_id" : "casing",
			"totalcount" : 12
		},
		{
			"_id" : "widgets",
			"totalcount" : 21
		}
	],
	"group" : [
		{
			"_id" : null,
			"totalcount" : 33
		}
	]
}

Comment by Ramon Fernandez Marina [ 25/Apr/17 ]

Thanks for your feature request paul.reed, I'm sending it to the Query team for evaluation.

I've fixed this ticket's description for you. For future tickets, look for a blue rectangle on the bottom left corner of text windows (like ticket description) to preview. Here's the JIRA formatting reference if you're curious about formatting options.

Regards,
Ramón.

Comment by Paul Reed [ 25/Apr/17 ]

ALSO - if I could have previewed this post before committing then I would have changed my formatting.
OR - if I could edit this item then I would have changed my formatting after posting.

Generated at Thu Feb 08 04:19:31 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.