A very strange behavior is happening to me with a query that I launch against MongoDb. This is the query:
aggregate - { "aggregate" : "makes", "pipeline" : [{ "$match" : { "countryIsoCode" : "ES" } }, { "$unwind" : "$models" }, { "$sort" : { "makeName" : 1, "models.modelName" : 1 } }, { "$group" : { "_id" : "$_id", "makeCode" : { "$first" : "$makeCode" }, "countryIsoCode" : { "$first" : "$countryIsoCode" }, "makeName" : { "$first" : "$makeName" }, "models" : { "$push" : "$models" } } }], "cursor" : { }, "$db" : "ModelInformation", "lsid" : { "id" : CSUUID("a676f361-d1db-40e9-8645-7f91e6eeda6f") } }
The answer obtained varies between 2 options randomly. They are the 2 answers that I show you below.
1.
aggregate - { "cursor" : { "firstBatch" : [{ "_id" : "f3766108-3196-411d-9292-2faa465211ba", "makeCode" : "SK1", "countryIsoCode" : "ES", "makeName" : "SKODA", "models" : [{ "id" : "f3939a64-8f07-45ee-9e01-e37a428bdbf3", "modelCode" : "299", "uniqueModelCode" : "SK00101", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 1831, "modelName" : "FABIA (6Y) 4/5P/COMBI (99-04)", "order" : 30, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "3fe12188-0ecd-4419-91be-33ff841e6ac3", "modelCode" : "354", "uniqueModelCode" : "SK00202", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 1910, "modelName" : "FELICIA (6U) 5P/COMBI (95-01)", "order" : 100, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "1452ed25-6b97-4746-84a2-588cf80388a9", "modelCode" : "143", "uniqueModelCode" : "SK00301", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 1567, "modelName" : "OCTAVIA (1U) 5P/COMBI (97-00)", "order" : 140, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }] }, { "_id" : "efef8c19-ce44-4215-9b71-6e028200b5f9", "makeCode" : "AR1", "countryIsoCode" : "ES", "makeName" : "ALFA ROMEO", "models" : [{ "id" : "d7cd74e0-43e8-4c6e-a988-fae755bd3d35", "modelCode" : "592", "uniqueModelCode" : "AR00402", "documentationType" : "intermediate", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 2452, "modelName" : "145/146 (930) 3/5P (94-01)", "order" : 90, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "76f52e13-22ed-475a-a77b-c4c2132288c8", "modelCode" : "959", "uniqueModelCode" : "AR00501", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : null, "commercialModelId" : 3499, "modelName" : "147 (937) 3/5P (00-10)", "order" : 120, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "4f51c588-2b67-4b23-9e10-dde0a9173a6d", "modelCode" : "944", "uniqueModelCode" : "AR00801", "documentationType" : "complete", "isNextGen" : false, "commercialModelId" : 3566, "modelName" : "159 (939) 4P/SW (05-)", "order" : 180, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "c41d338c-1fae-4a26-baf8-d2b87a4f518d", "modelCode" : "139", "uniqueModelCode" : "AR00701", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 2014, "modelName" : "166 (936) 4P (03-07)", "order" : 190, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }] }], "id" : NumberLong(0), "ns" : "ModelInformation.makes" }, "ok" : 1.0 }
aggregate - { "cursor" : { "firstBatch" : [{ "_id" : "efef8c19-ce44-4215-9b71-6e028200b5f9", "makeCode" : "AR1", "countryIsoCode" : "ES", "makeName" : "ALFA ROMEO", "models" : [{ "id" : "d7cd74e0-43e8-4c6e-a988-fae755bd3d35", "modelCode" : "592", "uniqueModelCode" : "AR00402", "documentationType" : "intermediate", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 2452, "modelName" : "145/146 (930) 3/5P (94-01)", "order" : 90, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "76f52e13-22ed-475a-a77b-c4c2132288c8", "modelCode" : "959", "uniqueModelCode" : "AR00501", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : null, "commercialModelId" : 3499, "modelName" : "147 (937) 3/5P (00-10)", "order" : 120, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "4f51c588-2b67-4b23-9e10-dde0a9173a6d", "modelCode" : "944", "uniqueModelCode" : "AR00801", "documentationType" : "complete", "isNextGen" : false, "commercialModelId" : 3566, "modelName" : "159 (939) 4P/SW (05-)", "order" : 180, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "c41d338c-1fae-4a26-baf8-d2b87a4f518d", "modelCode" : "139", "uniqueModelCode" : "AR00701", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 2014, "modelName" : "166 (936) 4P (03-07)", "order" : 190, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }] }, { "_id" : "f3766108-3196-411d-9292-2faa465211ba", "makeCode" : "SK1", "countryIsoCode" : "ES", "makeName" : "SKODA", "models" : [{ "id" : "f3939a64-8f07-45ee-9e01-e37a428bdbf3", "modelCode" : "299", "uniqueModelCode" : "SK00101", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 1831, "modelName" : "FABIA (6Y) 4/5P/COMBI (99-04)", "order" : 30, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "3fe12188-0ecd-4419-91be-33ff841e6ac3", "modelCode" : "354", "uniqueModelCode" : "SK00202", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 1910, "modelName" : "FELICIA (6U) 5P/COMBI (95-01)", "order" : 100, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }, { "id" : "1452ed25-6b97-4746-84a2-588cf80388a9", "modelCode" : "143", "uniqueModelCode" : "SK00301", "documentationType" : "complete", "isNextGen" : false, "vehicleType" : "tourism_minivan_a_b_c", "commercialModelId" : 1567, "modelName" : "OCTAVIA (1U) 5P/COMBI (97-00)", "order" : 140, "state" : "updated", "hasCesvimapPaintType" : true, "hasCentroZaragozaPaintType" : true, "hasManufacturerPaintType" : true, "hasAztPaintType" : true }] }], "id" : NumberLong(0), "ns" : "ModelInformation.makes" }, "ok" : 1.0 }
As you can see, the query tries to order by a field in the root of the document ("makeName") and by another field that is inside a nested array ("models.modelName"). For this, the documents are first "unwinded" and then regrouped (once they have been sorted). In the 2 random responses that Mongo returns, we see that the problem comes from the ordering with respect to the root field ("makeName") because the nested array is always returned correctly ordered.
Any idea or suggestion where the problem could be?