Query to return only matching array element

Hi @Fabio_Bracht and welcome in the MongoDB Community :muscle: !

Here is the document I inserted in my collection (same as your but with ObjectId instead of $oid).

db.col.insert({
    "_id": ObjectId("5f58b71b6f6d360f356a02ba"),
    "cnpjCpf": "97515035072",
    "producao": {
        "impressoras": [{
            "_id":
                ObjectId( "5f5a3c0cd84b5a2a8bd5d50c")
            ,
            "impressora": "Etirama",
            "z": [{
                "_id":
                    ObjectId("5f5a4f7cd6c4678dd0a533de")
                ,
                "z": "10",
                "modulo": "x",
                "facas": [{
                    "_id":
                        ObjectId("5f5a51642c26e32e008ecb12")
                    ,
                    "faca": "01",
                    "largura": "x"
                }, {
                    "_id":
                        ObjectId( "5f5a516f027b588538f52914")
                    ,
                    "faca": "02",
                    "largura": "y"
                }]
            }, {
                "_id":
                    ObjectId( "5f5a4fadd6c4678dd0a533df")
                ,
                "z": "20",
                "modulo": "x",
              "facas": [{
                    "_id":
                        ObjectId( "5f5a502dd6c4678dd0a533e2")
                    ,
                    "faca": "01",
                    "largura": "x"
                }, {
                    "_id":
                        ObjectId( "5f5a5078d6c4678dd0a533e3")
                    ,
                    "faca": "02",
                    "largura": "y"
                }]
            }, {
                "_id":
                    ObjectId("5f6a303c758a0f9e8a1219ae")
                ,
                "z": "100",
                "modulo": "1,5 mm",
                "desenvolvimento": "320",
                "encolhimento": "310",
                "distorcao": "96.875"
            }],
            "perfil": [{
                "_id":
                    ObjectId("5f5a4fbcd6c4678dd0a533e0")
                ,
                "perfil": "papel",
                "cores": {
                    "other": {
                        "curva": ["g34"],
                        "hd": ["hd05"],
                        "lineatura": ["112"]
                    }
                }
            }, {
                "_id":
                    ObjectId("5f5a4fded6c4678dd0a533e1")
                ,
                "perfil": "bopp",
                "cores": {
                    "cyan": {
                        "curva": ["h40"],
                        "hd": ["hd05"],
                        "lineatura": ["112"]
                    }
                }
            }]
        }, {
            "_id":
                ObjectId("5f6a3a395179f2a3c2c7ae05")
            ,
            "impressora": "Komexy",
            "banda": "Larga",
            "trap": ["0.05", "0.20", "0.25"],
            "espessura": ["1.14", "1.7"]
        }]
    }
})

Here is the aggregation pipeline I wrote:

[
  {
    '$match': {
      'producao.impressoras.z._id': new ObjectId('5f5a4f7cd6c4678dd0a533de')
    }
  }, {
    '$unwind': {
      'path': '$producao.impressoras'
    }
  }, {
    '$match': {
      'producao.impressoras.z._id': new ObjectId('5f5a4f7cd6c4678dd0a533de')
    }
  }, {
    '$unwind': {
      'path': '$producao.impressoras.z'
    }
  }, {
    '$match': {
      'producao.impressoras.z._id': new ObjectId('5f5a4f7cd6c4678dd0a533de')
    }
  }, {
    '$replaceRoot': {
      'newRoot': '$producao.impressoras.z'
    }
  }
]

And here is the result I get:

{
	"_id" : ObjectId("5f5a4f7cd6c4678dd0a533de"),
	"z" : "10",
	"modulo" : "x",
	"facas" : [
		{
			"_id" : ObjectId("5f5a51642c26e32e008ecb12"),
			"faca" : "01",
			"largura" : "x"
		},
		{
			"_id" : ObjectId("5f5a516f027b588538f52914"),
			"faca" : "02",
			"largura" : "y"
		}
	]
}

The intermediate $match are optional. Only the last one is really mandatory to get your result but the previous $match stages insure that we remove all the useless documents from the pipeline as soon as possible.
We can see this logic in MongoDB Compass:

It’s probably not the most optimized way to solve your query. But I think it does the job done.

I hope it helps and I’m curious if someone can find a better solution for this or not.

Cheers,
Maxime.

1 Like