How to combine two aggregations together to get one results?

Working with Data
1

I have two separate aggregations queries that work well.

First one pulls all the unique addresses (field name is “location”).
Second one counts how many times a street name is repeated. For example it prints out if John Street is used 1 time or 10 times. This is NOT REPEATS. This is just because there are many houses on the same street. “location” field content examples are:
1 John street
2 John street
5 John street
1 Peter street
8 Peter street
100 Yoyo street

How can I combine the below two aggregations so results of first aggregation is used by second aggregation and final result is printed?

1st aggregation:

[
{
$group: {
_id: “$location”,
count: { $sum: 1 },
},
},
{ $match: { count: { $gt: 0 } } },
{
$group: {
_id: null,
totalCount: { $sum: 1 },
content: { $push: “$$ROOT._id” },
},
},
{ $project: { _id: 0 } },
]

Second aggregation:

[
{
$addFields: {
parsedAddress: {
$arrayElemAt: [
{
$getField: {
field: “captures”,
input: {
$regexFind: {
input: “$location”,
regex: /\d+\s(.+)/,
},
},
},
},
0,
],
},
},
},
{
$group: {
_id: “$parsedAddress”,
count: {
$sum: 1,
},
},
},
]

Thanks

2

Hello @SHARP_CALL,

You can use $facet stage to process multiple aggregation pipelines, but make sure the result should not exceed the 16 MB BSON document size limit.

3

Thanks.

Can you add my above written aggregations into one so I can see the format? I am new to mongodb and don’t know what fields etc are but that should help me get started on this.

4

Hello,

The provided docs link has everything with examples, if you are new then I would suggest you read and implement this in your use case, let me know if you are getting any issues.

You can implement something like this,

{
  $facet: {
    first_agg: [
      // add first aggregation stages
    ],
    second_agg: [
      // add second aggregation stages
    ]
  }
}
5

The brackets in the sample you provided do not match the ones I have. I have put it like this but I get many errors:

Unexpected token, expected “]” (10:3)

There are probably multiple line problems but to being with that 10:3 is the problem. Also, my first stage is an array. Should that be a problem?

Below is how I combined them. Maybe it is the brackets and you can see the issue?

> {
>   $facet: {
> 
> [
>   {
>     $group: {
>       _id: "$location",
>       count: { $sum: 1 },
>     },
>   },
>   { $match: { count: { $gt: 0 } } },
>   {
>     $group: {
>       _id: null,
>       totalCount: { $sum: 1 },
>       content: { $push: "$$ROOT._id" },
>     },
>   },
>   { $project: { _id: 0 } },
> ],
> 
> [
>   {
>     $addFields: {
>       parsedAddress: {
>         $arrayElemAt: [
>           {
>             $getField: {
>               field: "captures",
>               input: {
>                 $regexFind: {
>                   input: "$location",
>                   regex: /\d+\s(.+)/,
>                 },
>               },
>             },
>           },
>           0,
>         ],
>       },
>     },
>   },
>   {
>     $group: {
>       _id: "$parsedAddress",
>       count: {
>         $sum: 1,
>       },
>     },
>   },
> ],
>    }
> }
6

I would suggest you follow the documentation and experiment with the examples provided in the documentation,

You did not use the correct brackets, well it is a basic for every programming language,

If you refer to the provided syntax in my above post, there are 2 properties first_agg and second_agg, both are itself aggregation pipelines and already bounded with the brackets, you just need to put your stages inside.

Your query would be something like this,

{
  $facet: {
    first_agg: [
      { $group: ... },
      { $match: ... },
      { $group: ... },
      { $project: ... }
    ],
    second_agg: [
      { $addFields: ... },
      { $group: ... }
    ]
  }
}
1 Like