Deleting Records in Nested BSON Array from MongoDB
- By : Mydatahack
- Category : DBA, Infrastructure
- Tags: Complex Query, Delete Record, MongoDB
When you are trying to manipulate data within nested BSON array in MongoDB, things get complex. Luckily, MongoDB has the ability to pass a JavaScript-like function to forEach to manipulate or query data. Let’s take a look at the data below.
Sample Data
{ "_id": ObjectId("5a2f38458bcodgerce87vds"), "customerId": "45632", "cart": { "cartId": "e235", "items": [ { "itemId": "Ä1234", "count": 3, "unitprice": 800 }, { "itemId": null, "count": 3, "unitprice": 800 } ] } }, { "_id": ObjectId("5a2f384345psdflver87vds"), "customerId": "584932", "cart": { "cartId": "e237", "items": [ { "itemId": "Ä345", "count": 8, "unitprice": 457 }, { "itemId": null, "count": 3, "unitprice": 789.6 } ] } }
We would like to delete only the item with null itemId form the database. When you look at the data, it is pretty complex to write a query. This cannot be done with a regular MongoDb query. But, it is possible because we can pass a function while looping the documents with forEach.
Queries
(1) Find customers with null itemId
This is a straight forward MongoDb query to find the record that matches with nested bson array value.
db.getCollection('Transaction') .find({'cart.items': {$elemMatch:{'itemId': null}}});
(2) Get the null itemId list
This is how we can use forEach and pass function to print out the null itemId records.
db.getCollection('Transaction') .find({'cart.items': {$elemMatch:{'itemId': null}}}) .forEach(function (document) { var items = cart.items; var len = items.length; for (var i = 0; i < len; i++) { if (items[i].itemId == null) { print(items[i]) } } });
(3) Delete only null itemId items
This is the magic query to delete only null itemId items. The trick is to create an index array to store the index for null id items and then delete them from the actual array.
db.getCollection('Transaction') .find({'cart.items': {$elemMatch:{'itemId': null}}}) .forEach(function (document) { var items = document.cart.items; var len = items.length; var indexArray = []; for (var i = 0; i < len; i++) { if (items[i].itemId == null) { indexArray.push(i) print('Deleted Records adding index to index array: ',document.customerId,',', i) } } indexArray.forEach( function(index) { print('Deleted Records according to index array: ',document.customerId,',', index) items.pop(index); }) db.Transaction.save(document); });
I think this is pretty cool.