Query to return only matching array element

Working with Data
1

Hello, I would like to know how I can query a array producao.impressoras..z.._ id, and receive only the content of the object that matches the _id above? in producao.impressoras.$._id I got it.

My document:

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

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:

image
image1272×1202 128 KB

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
3

hello, this solved the problem. Now I saw that I have another one, how to update this data?
I updated with aggregate and $ set, but it didn’t save.

[{
    '$match': {
        '_id': new ObjectId('5f58b71b6f6d360f356a02ba')
    }
}, {
    '$project': {
        'impressora': {
            '$filter': {
                'input': '$producao.impressoras',
                'cond': {
                    '$eq': [
                        '$$this._id', new ObjectId('5f5a3c0cd84b5a2a8bd5d50c')
                    ]
                }
            }
        }
    }
}, {
    '$unwind': {
        'path': '$impressora'
    }
}, {
    '$project': {
        'z': {
            '$filter': {
                'input': '$impressora.z',
                'cond': {
                    '$eq': [
                        '$$this._id', new ObjectId('5f5a4f7cd6c4678dd0a533de')
                    ]
                }
            }
        }
    }
}, {
    '$unwind': {
        'path': '$z'
    }
}, {
    '$project': {
        'z.facas': 0,
        '_id': 0
    }
}, {
    '$set': {
        'z.z': 22
    }
}]
4

$set is an alias for $addFields which is just adding fields into the documents at this stage in the pipeline.

Documents in a pipeline are completely disconnected from the documents in the collections. You can’t update them this way.

You must use the update operation and most probably the array operators in your case.

5

Hello : )

Data,1 document,but works if many in collection,it only updates the “_id”: “5f58b71b6f6d360f356a02ba”

I updated
ROOT when “_id”: “5f58b71b6f6d360f356a02ba”
“producao”
“impressoras” when “_id”: “5f5a3c0cd84b5a2a8bd5d50c”
“z” when “_id”: “5f5a4f7cd6c4678dd0a533de”
facas when “_id”: “5f5a51642c26e32e008ecb12”
“largura”: “NEW LARGURA!!” (i added this)

In update pipelines the output of the pipeline,must be the updated document.
Its alot nested so may look complecated but its always the same thing.

When i update a document i do
“$mergeObjects” the_doc_i_have {:updatedfield1 …}

When i update a array i do
“$map” and if match the filter,i replace the member

This way the result is what i had + the updates i made.

This looks complecated because its alot nested,and its raw json,
with function use that wrap the json it can be much more simple.
If time i might resend simpler code with functions.
Drivers also provide query builders that help.

If anyone knows a better way,would be helpful also.

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

Query

{
  "update": "testcoll",
  "updates": [
    {
      "q": {},
      "u": [
        {
          "$replaceRoot": {
            "newRoot": {
              "$cond": [
                {
                  "$eq": [
                    "$$ROOT._id",
                    "5f58b71b6f6d360f356a02ba"
                  ]
                },
                {
                  "$mergeObjects": [
                    "$$ROOT",
                    {
                      "producao": {
                        "$mergeObjects": [
                          "$$ROOT.producao",
                          {
                            "impressoras": {
                              "$map": {
                                "input": "$$ROOT.producao.impressoras",
                                "as": "impressora",
                                "in": {
                                  "$cond": [
                                    {
                                      "$eq": [
                                        "$$impressora._id",
                                        "5f5a3c0cd84b5a2a8bd5d50c"
                                      ]
                                    },
                                    {
                                      "$mergeObjects": [
                                        "$$impressora",
                                        {
                                          "z": {
                                            "$map": {
                                              "input": "$$impressora.z",
                                              "as": "zmember",
                                              "in": {
                                                "$cond": [
                                                  {
                                                    "$eq": [
                                                      "$$zmember._id",
                                                      "5f5a4f7cd6c4678dd0a533de"
                                                    ]
                                                  },
                                                  {
                                                    "$mergeObjects": [
                                                      "$$zmember",
                                                      {
                                                        "facas": {
                                                          "$map": {
                                                            "input": "$$zmember.facas",
                                                            "as": "faca",
                                                            "in": {
                                                              "$cond": [
                                                                {
                                                                  "$eq": [
                                                                    "$$faca._id",
                                                                    "5f5a51642c26e32e008ecb12"
                                                                  ]
                                                                },
                                                                {
                                                                  "$mergeObjects": [
                                                                    "$$faca",
                                                                    {
                                                                      "largura": "NEW LARGURA!!"
                                                                    }
                                                                  ]
                                                                },
                                                                "$$faca"
                                                              ]
                                                            }
                                                          }
                                                        }
                                                      }
                                                    ]
                                                  },
                                                  "$$zmember"
                                                ]
                                              }
                                            }
                                          }
                                        }
                                      ]
                                    },
                                    "$$impressora"
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      }
                    }
                  ]
                },
                "$$ROOT"
              ]
            }
          }
        }
      ],
      "multi": true
    }
  ]
}

Result

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

Hope it helps

6

thank you all! This solved.
I did it in nodejs, not exactly like that, but I transcribed it for general use:

const MongoClient = require('mongodb').MongoClient, { ObjectId } = require('mongodb'),
    queryDb = { 'producao.impressoras.z._id': new ObjectId('5f5a4f7cd6c4678dd0a533de') },
    updateDocument = {
        '$set': {
            'producao.impressoras.$.z.$[pressZ].zCilindro': 1,
            'producao.impressoras.$.z.$[pressZ].modulo': 1,
            'producao.impressoras.$.z.$[pressZ].desenvolvimento': 1,
            'producao.impressoras.$.z.$[pressZ].encolhimento': 1,
            'producao.impressoras.$.z.$[pressZ].distorcao': 1
        }
    },
    options = { 'arrayFilters': [{ 'pressZ._id': new ObjectId('5f5a4f7cd6c4678dd0a533de') }] };
MongoClient.connect(
    'mongodb://...', { useNewUrlParser: true, useUnifiedTopology: true },
    function(connectErr, client) {
        const coll = client.db('flexograv').collection('customers'),
        r = coll.updateOne(queryDb, updateDocument, options);
        console.log(r);
        client.close();
    });
7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.