Improve performance aggregation count

Working with Data
, ,
1

Hi to all,
on a standalone server of MongoDB 4.2

i have a collection with 23 Millions of document like this

{
  "_id": "644932e30ce7a25b142bca1c",
  "data": {
    "Assistenza": {
      "Eventi": {
        "Erogazione": [
          {
            "TipoOperatore": "8",
            "data": "1672963200000",
            "Prestazioni": [
              {
                "TipoPrestazione": 3,
                "numPrestazione": 1
              }
            ]
          }
        ],
        "PresaInCarico": {
          "Id_Rec": "1902092023-01-05CF_0000000000001",
          "data": "1672876800000"
        }
      },
      "Erogatore": {
        "CodiceRegione": "190",
        "CodiceASL": "209"
      },
      "Trasmissione": {
        "tipo": "I"
      }
    }
  },
  "idFlusso": "644932cf0ce7a25b142bc632",
  "idTracciato": "574",
  "idUtente": "2",
  "dataCreazione": "2023-04-26T14:18:30.495Z",
  "jobId": "18221",
  "stato": "VERSIONED",
  "metadata": {
    "nomeFile": "SIAD_APS_600000_I.xml",
    "periodoRiferimento": "01",
    "annoRiferimento": "2023",
    "idRegione": "33",
    "valoreRegione": "190",
    "periodoRiferimentoInTrimestre": "1",
    "periodoRiferimentoInSemestre": "1",
    "periodoRiferimentoInAnno": "1",
    "Assistenza_Eventi_PresaInCarico_Id_Rec_encrypted": "",
    "idAzienda": "485",
    "valoreAzienda": "190209"
  },
  "key": {
    "Assistenza-Erogatore-CodiceASL": "209",
    "Assistenza-Erogatore-CodiceRegione": "190",
    "Assistenza-Eventi-Erogazione[0]-TipoOperatore": "8",
    "Assistenza-Eventi-Erogazione[0]-data": "1672963200000",
    "Assistenza-Eventi-PresaInCarico-Id_Rec": "1902092023-01-05CF_0000000000001",
    "Assistenza-Eventi-PresaInCarico-data": "1672876800000",
    "stato": "VERSIONED"
  },
  "progressivo": "2",
  "flagVersioneMassima": true,
  "progressivoMassimo": "2",
  "sessioniControllo": [
    {
      "jobId": "18453",
      "idUtente": "2",
      "dataElaborazioneControllo": "2023-05-25T09:10:31.331Z",
      "errori": [
        {
          "_id": "646f27193a95004ca8858aac",
          "idUtente": "2",
          "dataElaborazioneControllo": "2023-05-25T09:10:31.331Z",
          "risultatoOperazione": [
            {
              "idControllo": "4613",
              "codControllo": "FAKE1",
              "descControllo": "FAKE1",
              "idErrore": "6603",
              "codErrore": "ANOMALIA",
              "descErrore": "Anomalia",
              "ambitoErrore": "A",
              "gravitaErrore": "LIEVE",
              "riferimentoErrore": "CAMPO",
              "tipoErrore": "ANOMALIA",
              "idCampo": "44426"
            }
          ]
        },
        {
          "_id": "646f28e53a95004ca88eb26e",
          "idUtente": "2",
          "dataElaborazioneControllo": "2023-05-25T09:10:31.331Z",
          "risultatoOperazione": [
            {
              "idControllo": "4620",
              "codControllo": "FAKE2",
              "descControllo": "FAKE2",
              "idErrore": "6603",
              "codErrore": "ANOMALIA",
              "descErrore": "Anomalia",
              "ambitoErrore": "A",
              "gravitaErrore": "LIEVE",
              "riferimentoErrore": "CAMPO",
              "tipoErrore": "ANOMALIA",
              "idCampo": "44426"
            }
          ]
        },
        {
          "_id": "646f2aa93a95004ca897da2e",
          "idUtente": "2",
          "dataElaborazioneControllo": "2023-05-25T09:10:31.331Z",
          "risultatoOperazione": [
            {
              "idControllo": "4621",
              "codControllo": "FAKE3",
              "descControllo": "FAKE3",
              "idErrore": "6603",
              "codErrore": "ANOMALIA",
              "descErrore": "Anomalia",
              "ambitoErrore": "A",
              "gravitaErrore": "LIEVE",
              "riferimentoErrore": "CAMPO",
              "tipoErrore": "ANOMALIA",
              "idCampo": "44426"
            }
          ]
        }
      ]
    },
    {
      "jobId": "18455",
      "idUtente": "2",
      "dataElaborazioneControllo": "2023-05-25T13:21:31.870Z"
    }
  ]
}

and execute aggregation

db.flussi_dettagli.aggregate(
    [
			{
				"$match" : {
					"idTracciato" : Long("574")
				}
			},
			{
				"$match" : {
					"sessioniControllo.jobId" : Long("18453")
				}
			},
			{
				"$unwind" : {
					"path" : "$sessioniControllo",
					"preserveNullAndEmptyArrays" : false
				}
			},
			{
				"$match" : {
					"sessioniControllo.jobId" : Long("18453")
				}
			},
			{
				"$unwind" : {
					"path" : "$sessioniControllo.errori",
					"preserveNullAndEmptyArrays" : false
				}
			},
			{
				"$unwind" : {
					"path" : "$sessioniControllo.errori.risultatoOperazione",
					"preserveNullAndEmptyArrays" : false
				}
			},
			{
				"$count" : "count"
			}
		]
    )

the first match on sessioniControllo.jobId is for force mongo to use index that i have on {idTracciato:1,sessioniControllo.jobId: 1}

the count ran in about 17 sec.

below the explain:

{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
					"$and" : [
						{
							"idTracciato" : 574
						},
						{
							"sessioniControllo.jobId" : 18453
						}
					]
				},
				"fields" : {
					"sessioniControllo" : 1,
					"_id" : 0
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "siact.flussi_dettagli",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"$and" : [
							{
								"idTracciato" : {
									"$eq" : 574
								}
							},
							{
								"sessioniControllo.jobId" : {
									"$eq" : 18453
								}
							}
						]
					},
					"queryHash" : "6E14D4E2",
					"planCacheKey" : "1F663360",
					"winningPlan" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"idTracciato" : 1,
								"sessioniControllo.jobId" : 1
							},
							"indexName" : "idx_idTracciato_sessioneControllo",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"idTracciato" : [ ],
								"sessioniControllo.jobId" : [ "sessioniControllo" ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"idTracciato" : [ "[574, 574]" ],
								"sessioniControllo.jobId" : [ "[18453, 18453]" ]
							}
						}
					},
					"rejectedPlans" : [
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18453
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1,
									"data.informazioniRicovero.codiceIstitutoDiCura" : 1,
									"data.informazioniRicovero.progressivoSDO" : 1
								},
								"indexName" : "idx_discard_no_encrypted_573_572",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ],
									"data.informazioniRicovero.codiceIstitutoDiCura" : [ ],
									"data.informazioniRicovero.progressivoSDO" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"data.informazioniRicovero.codiceIstitutoDiCura" : [ "[MinKey, MaxKey]" ],
									"data.informazioniRicovero.progressivoSDO" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18453
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"dataCreazione" : 1,
									"stato" : 1,
									"flagVersioneMassima" : 1
								},
								"indexName" : "idx_max_version_with_data",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"dataCreazione" : [ ],
									"stato" : [ ],
									"flagVersioneMassima" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"dataCreazione" : [ "[MinKey, MaxKey]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"flagVersioneMassima" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18453
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1
								},
								"indexName" : "idTracciato_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18453
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1,
									"data.FlsResSemires_2.PrestazioniSR.tempoParziale" : 1,
									"data.FlsResSemires_2.Chiave.tipoPrestazione" : 1,
									"data.FlsResSemires_2.Chiave.Data" : 1,
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceStruttura" : 1,
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceASL" : 1,
									"data.CodiceRegione" : 1,
									"metadata.FlsResSemires_2_Chiave_ID_REC_encrypted" : 1,
									"data.FlsResSemires_2.Dimissione.Data" : 1,
									"data.FlsResSemires_2.PrestazioniSR.tempoPieno" : 1
								},
								"indexName" : "idx_discard_encrypted_567_566",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ],
									"data.FlsResSemires_2.PrestazioniSR.tempoParziale" : [ ],
									"data.FlsResSemires_2.Chiave.tipoPrestazione" : [ ],
									"data.FlsResSemires_2.Chiave.Data" : [ ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceStruttura" : [ ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceASL" : [ ],
									"data.CodiceRegione" : [ ],
									"metadata.FlsResSemires_2_Chiave_ID_REC_encrypted" : [ ],
									"data.FlsResSemires_2.Dimissione.Data" : [ ],
									"data.FlsResSemires_2.PrestazioniSR.tempoPieno" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.PrestazioniSR.tempoParziale" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.tipoPrestazione" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.Data" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceStruttura" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceASL" : [ "[MinKey, MaxKey]" ],
									"data.CodiceRegione" : [ "[MinKey, MaxKey]" ],
									"metadata.FlsResSemires_2_Chiave_ID_REC_encrypted" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Dimissione.Data" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.PrestazioniSR.tempoPieno" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18453
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1,
									"flagVersioneMassima" : 1
								},
								"indexName" : "idx_max_version",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ],
									"flagVersioneMassima" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"flagVersioneMassima" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18453
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1
								},
								"indexName" : "idTracciato_1_stato_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ]
								}
							}
						}
					]
				},
				"executionStats" : {
					"executionSuccess" : true,
					"nReturned" : 600000,
					"executionTimeMillis" : 14552,
					"totalKeysExamined" : 600000,
					"totalDocsExamined" : 600000,
					"executionStages" : {
						"stage" : "FETCH",
						"nReturned" : 600000,
						"executionTimeMillisEstimate" : 408,
						"works" : 600001,
						"advanced" : 600000,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 5572,
						"restoreState" : 5572,
						"isEOF" : 1,
						"docsExamined" : 600000,
						"alreadyHasObj" : 0,
						"inputStage" : {
							"stage" : "IXSCAN",
							"nReturned" : 600000,
							"executionTimeMillisEstimate" : 115,
							"works" : 600001,
							"advanced" : 600000,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 5572,
							"restoreState" : 5572,
							"isEOF" : 1,
							"keyPattern" : {
								"idTracciato" : 1,
								"sessioniControllo.jobId" : 1
							},
							"indexName" : "idx_idTracciato_sessioneControllo",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"idTracciato" : [ ],
								"sessioniControllo.jobId" : [ "sessioniControllo" ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"idTracciato" : [ "[574, 574]" ],
								"sessioniControllo.jobId" : [ "[18453, 18453]" ]
							},
							"keysExamined" : 600000,
							"seeks" : 1,
							"dupsTested" : 600000,
							"dupsDropped" : 0,
							"indexDef" : {
								"indexName" : "idx_idTracciato_sessioneControllo",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"sessioniControllo.jobId" : [ "sessioniControllo" ]
								},
								"keyPattern" : {
									"idTracciato" : 1,
									"sessioniControllo.jobId" : 1
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"direction" : "forward"
							}
						}
					}
				}
			}
		},
		{
			"$unwind" : {
				"path" : "$sessioniControllo"
			}
		},
		{
			"$match" : {
				"sessioniControllo.jobId" : {
					"$eq" : 18453
				}
			}
		},
		{
			"$unwind" : {
				"path" : "$sessioniControllo.errori"
			}
		},
		{
			"$unwind" : {
				"path" : "$sessioniControllo.errori.risultatoOperazione"
			}
		},
		{
			"$group" : {
				"_id" : {
					"$const" : null
				},
				"count" : {
					"$sum" : {
						"$const" : 1
					}
				}
			}
		},
		{
			"$project" : {
				"_id" : false,
				"count" : true
			}
		}
	],
	"ok" : 1
}
2

Just some thoughts.

1 - may be you can merge your 2 initial $match into a single one
2 - your document seems pretty big so it might be useful to $project only what you $unwind before $unwind so that it reduce the memory usage
3 - you $unwind sessioniControllo and then $match, may be you could $filter and then $unwind and forgo the $match, this way only what $match’es is $unwind’ed.
4 - you could possibly replace the last $unwind with a $reduce and then $sum the result rather than $count

3

Hi Steeve,
i tried with

db.flussi_dettagli.aggregate([
    {
        "$match": {
            "idTracciato": Long("574"),
            "sessioniControllo.jobId": Long("18453") // if not put this condition not exclude documents that not have "sessioniControllo"
        }
    },
    {
        "$project": {
            "sessioniControllo": {
                $filter: {
                    input: "$sessioniControllo",
                    as: "elem",
                    cond: { $eq: ["$$elem.jobId", Long("18453")] }
                }
            },
        }
    },
    {
        $project: {
            "totale": {
                "$reduce": {
                    input: "$sessioniControllo",
                    initialValue: 0,
                    in: {
                        $add: ["$$value",
                            {
                                "$reduce": {
                                    input: "$$this.errori",
                                    initialValue: 0,
                                    in: {
                                        $add: ["$$value", { $size: "$$this.risultatoOperazione" }]
                                    }
                                }
                            }
                        ]
                    }
                }
            }
        }
    },
    {
        $group: {
            _id: null,
            total: {
                $sum: "$totale" // campo da sommare
            }
        }
    }
]
)
    ;

the query ran in 10 sec., so is better but slow

the result documents before unwind are 600.000, after all unwind are 1800000

4

some attempts:

db.flussi_dettagli.aggregate(
    [
        {
            "$match": {
                "idTracciato": Long("574"),
                "sessioniControllo.jobId": Long("18453")
            }
        },
        {
            "$count": "count"
        }
    ])
    ;

656 millisecond

db.flussi_dettagli.aggregate(
    [
        {
            "$match": {
                "idTracciato": Long("574"),
                "sessioniControllo.jobId": Long("18453")
            }
        },
        {
            "$project": {
                "sessioniControllo": {
                    $filter: {
                        input: "$sessioniControllo",
                        as: "elem",
                        cond: { $eq: ["$$elem.jobId", Long("18453")] }
                    }
                },
            }
        },
        {
            "$unwind": {
                "path": "$sessioniControllo",
                "preserveNullAndEmptyArrays": false
            }
        },
        {
            "$count": "count"
        }
    ])
    ;

8,5 second

db.flussi_dettagli.aggregate(
    [
        {
            "$match": {
                "idTracciato": Long("574"),
                "sessioniControllo.jobId": Long("18453")
            }
        },
        {
            "$unwind": {
                "path": "$sessioniControllo",
                "preserveNullAndEmptyArrays": false
            }
        },
        {
            "$match": {
                "sessioniControllo.jobId": Long("18453")
            }
        },
        {
            "$count": "count"
        }
    ])
    ;

11 second

db.flussi_dettagli.aggregate(
    [
        {
            "$match": {
                "idTracciato": Long("574"),
                "sessioniControllo.jobId": Long("18453")
            }
        },
        {
            "$project": {
                "sessioniControllo": {
                    $filter: {
                        input: "$sessioniControllo",
                        as: "elem",
                        cond: { $eq: ["$$elem.jobId", Long("18453")] }
                    }
                },
            }
        },
        {
            "$unwind": {
                "path": "$sessioniControllo",
                "preserveNullAndEmptyArrays": false
            }
        },
        {
            "$unwind": {
                "path": "$sessioniControllo.errori",
                "preserveNullAndEmptyArrays": false
            }
        },
        {
            "$unwind": {
                "path": "$sessioniControllo.errori.risultatoOperazione",
                "preserveNullAndEmptyArrays": false
            }
        },
        {
            "$count": "count"
        }
    ])
    ;

11 second.

it seems that the most expensive stage is the first unwind

5

The $unwind stage is expensive because as you have seen

that is why avoid it reduce the memory consumption and work to do.

The fact that with $match and $count is really fast:

means that you indexes work.

One thing you could try is to move the $filter of the first $project into the input: value of the second $project. So you would $match and then:

{
        $project: {
            "totale": {
                "$reduce": {
                    input: { $filter: {
                        input: "$sessioniControllo",
                        as: "elem",
                        cond: { $eq: ["$$elem.jobId", Long("18453")] }
                    } }
                    initialValue: 0,
                    in: {
                        $add: ["$$value",
                            {
                                "$reduce": {
                                    input: "$$this.errori",
                                    initialValue: 0,
                                    in: {
                                        $add: ["$$value", { $size: "$$this.risultatoOperazione" }]
                                    }
                                }
                            }
                        ]
                    }
                }
            }
        }
    }

It is the same code but run in a single stage. I usually prefer simpler stages as it is easier to code, to debug, to read and to understand. But if my preferences hinder performance, then hell with my preferences.

A slightly more complex alternative of the above would be to still use $sessioniControllo as the top input: but use the $cond expression (from the $filter) to only $add the matching jobId.

6

i tried with one stage project, but performance is not better

i don’t understand, please can you explain?

7

I give some more information

the result of query is used two times

  • one for give single page ( 16 records ) and is immediate
  • one, with added stage $count to have total record for pagination

the user want that total records is always showed ( don’t want “1 of more” )

i tried $facet operator too, but don’t have relevant changes

8

Basically, the top level $reduce $add (using $cond) 0 if the jobId does not match but $add the inner most $reduce if it does.

{
        $project: {
            "totale": {
                "$reduce": {
                    input: "$sessioniControllo" ,
                    initialValue: 0 ,
                    in: {
                        $add: [ "$$value",
                            { "cond" : [
                                { "$ne" : [ "$$this.jobId", Long("18453") ] } ,
                                0 ,
                                { "$reduce" : {
                                    input: "$$this.errori" ,
                                    initialValue: 0 ,
                                    in: {
                                        $add: [ "$$value" , { $size: "$$this.risultatoOperazione" } ]
                                    }
                                } }
                            ] } 
                        ]
                    }
                }
            }
        }
    }
9

unfortunatly no better performance :frowning:

maybe i would consider to store information already unwinded in others collection… but… i’m not sure…

10

finally I agreed with the user to only count the first 100,000 records

if the records are more i will not show the total page number.

But i have another question, if i ran the same query without $count but with

			{
				"$skip" : 0
			},
			{
				"$limit" : 16
			}

if i have at least 16 records as result, the query ran in 162ms

if result is 0 records, the query ran in 25 seconds… i think because mongo fetch all 600.000 records…

looking example i put in first post,
with jobId 18453 found records with sessioniControllo.errori … and is fast
with jobId 18455 not found records with sessioniControllo.errori … and is slow

11

i tried not use $unwind ( only to find if i have results or not )
but the result is the same ( 27 seconds )

db.flussi_dettagli.aggregate([
    {
        "$match": {
            "idTracciato": Long("574"),
            "sessioniControllo.jobId": Long("18455") // if not put this condition not exclude documents that not have "sessioniControllo"
        }
    },
    {
        "$project": {
            "sessioniControllo": {
                $filter: {
                    input: "$sessioniControllo",
                    as: "elem",
                    cond: { $eq: ["$$elem.jobId", Long("18455")] }
                }
            },
        }
    },
    {
        "$match": {
            "sessioniControllo.errori": {
                "$exists": true
            }
        }
    },
]
)
    ;

explain:

{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
					"idTracciato" : 574,
					"sessioniControllo.jobId" : 18455
				},
				"fields" : {
					"sessioniControllo" : 1,
					"_id" : 1
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "siact.flussi_dettagli",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"$and" : [
							{
								"idTracciato" : {
									"$eq" : 574
								}
							},
							{
								"sessioniControllo.jobId" : {
									"$eq" : 18455
								}
							}
						]
					},
					"queryHash" : "6E14D4E2",
					"planCacheKey" : "39D753AB",
					"winningPlan" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"idTracciato" : 1,
								"sessioniControllo.jobId" : 1
							},
							"indexName" : "idx_idTracciato_sessioneControllo",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"idTracciato" : [ ],
								"sessioniControllo.jobId" : [ "sessioniControllo" ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"idTracciato" : [ "[574, 574]" ],
								"sessioniControllo.jobId" : [ "[18455, 18455]" ]
							}
						}
					},
					"rejectedPlans" : [
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18455
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1
								},
								"indexName" : "idTracciato_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18455
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1,
									"data.FlsResSemires_2.PrestazioniSR.tempoParziale" : 1,
									"data.FlsResSemires_2.Chiave.tipoPrestazione" : 1,
									"data.FlsResSemires_2.Chiave.Data" : 1,
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceStruttura" : 1,
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceASL" : 1,
									"data.CodiceRegione" : 1,
									"metadata.FlsResSemires_2_Chiave_ID_REC_encrypted" : 1,
									"data.FlsResSemires_2.Dimissione.Data" : 1,
									"data.FlsResSemires_2.PrestazioniSR.tempoPieno" : 1
								},
								"indexName" : "idx_discard_encrypted_567_566",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ],
									"data.FlsResSemires_2.PrestazioniSR.tempoParziale" : [ ],
									"data.FlsResSemires_2.Chiave.tipoPrestazione" : [ ],
									"data.FlsResSemires_2.Chiave.Data" : [ ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceStruttura" : [ ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceASL" : [ ],
									"data.CodiceRegione" : [ ],
									"metadata.FlsResSemires_2_Chiave_ID_REC_encrypted" : [ ],
									"data.FlsResSemires_2.Dimissione.Data" : [ ],
									"data.FlsResSemires_2.PrestazioniSR.tempoPieno" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.PrestazioniSR.tempoParziale" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.tipoPrestazione" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.Data" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceStruttura" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Chiave.Erogatore.CodiceASL" : [ "[MinKey, MaxKey]" ],
									"data.CodiceRegione" : [ "[MinKey, MaxKey]" ],
									"metadata.FlsResSemires_2_Chiave_ID_REC_encrypted" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.Dimissione.Data" : [ "[MinKey, MaxKey]" ],
									"data.FlsResSemires_2.PrestazioniSR.tempoPieno" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18455
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1,
									"data.informazioniRicovero.codiceIstitutoDiCura" : 1,
									"data.informazioniRicovero.progressivoSDO" : 1
								},
								"indexName" : "idx_discard_no_encrypted_573_572",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ],
									"data.informazioniRicovero.codiceIstitutoDiCura" : [ ],
									"data.informazioniRicovero.progressivoSDO" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"data.informazioniRicovero.codiceIstitutoDiCura" : [ "[MinKey, MaxKey]" ],
									"data.informazioniRicovero.progressivoSDO" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18455
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"dataCreazione" : 1,
									"stato" : 1,
									"flagVersioneMassima" : 1
								},
								"indexName" : "idx_max_version_with_data",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"dataCreazione" : [ ],
									"stato" : [ ],
									"flagVersioneMassima" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"dataCreazione" : [ "[MinKey, MaxKey]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"flagVersioneMassima" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18455
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1
								},
								"indexName" : "idTracciato_1_stato_1",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"filter" : {
								"sessioniControllo.jobId" : {
									"$eq" : 18455
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"stato" : 1,
									"flagVersioneMassima" : 1
								},
								"indexName" : "idx_max_version",
								"isMultiKey" : false,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"stato" : [ ],
									"flagVersioneMassima" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"stato" : [ "[MinKey, MaxKey]" ],
									"flagVersioneMassima" : [ "[MinKey, MaxKey]" ]
								}
							}
						},
						{
							"stage" : "FETCH",
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"idTracciato" : 1,
									"sessioniControllo.jobId" : 1,
									"sessioniControllo.errori" : 1
								},
								"indexName" : "idx_idTracciato_sessioneControllo_errori",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"sessioniControllo.jobId" : [ "sessioniControllo" ],
									"sessioniControllo.errori" : [ "sessioniControllo", "sessioniControllo.errori" ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"idTracciato" : [ "[574, 574]" ],
									"sessioniControllo.jobId" : [ "[18455, 18455]" ],
									"sessioniControllo.errori" : [ "[MinKey, MaxKey]" ]
								}
							}
						}
					]
				},
				"executionStats" : {
					"executionSuccess" : true,
					"nReturned" : 600000,
					"executionTimeMillis" : 21741,
					"totalKeysExamined" : 600000,
					"totalDocsExamined" : 600000,
					"executionStages" : {
						"stage" : "FETCH",
						"nReturned" : 600000,
						"executionTimeMillisEstimate" : 333,
						"works" : 600001,
						"advanced" : 600000,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 6977,
						"restoreState" : 6977,
						"isEOF" : 1,
						"docsExamined" : 600000,
						"alreadyHasObj" : 0,
						"inputStage" : {
							"stage" : "IXSCAN",
							"nReturned" : 600000,
							"executionTimeMillisEstimate" : 131,
							"works" : 600001,
							"advanced" : 600000,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 6977,
							"restoreState" : 6977,
							"isEOF" : 1,
							"keyPattern" : {
								"idTracciato" : 1,
								"sessioniControllo.jobId" : 1
							},
							"indexName" : "idx_idTracciato_sessioneControllo",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"idTracciato" : [ ],
								"sessioniControllo.jobId" : [ "sessioniControllo" ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"idTracciato" : [ "[574, 574]" ],
								"sessioniControllo.jobId" : [ "[18455, 18455]" ]
							},
							"keysExamined" : 600000,
							"seeks" : 1,
							"dupsTested" : 600000,
							"dupsDropped" : 0,
							"indexDef" : {
								"indexName" : "idx_idTracciato_sessioneControllo",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"idTracciato" : [ ],
									"sessioniControllo.jobId" : [ "sessioniControllo" ]
								},
								"keyPattern" : {
									"idTracciato" : 1,
									"sessioniControllo.jobId" : 1
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"direction" : "forward"
							}
						}
					}
				}
			}
		},
		{
			"$project" : {
				"_id" : true,
				"sessioniControllo" : {
					"$filter" : {
						"input" : "$sessioniControllo",
						"as" : "elem",
						"cond" : {
							"$eq" : [
								"$$elem.jobId",
								{
									"$const" : 18455
								}
							]
						}
					}
				}
			}
		},
		{
			"$match" : {
				"sessioniControllo.errori" : {
					"$exists" : true
				}
			}
		}
	],
	"ok" : 1
}