mongodb query takes too long time

Question

I have following documents in my mongodb collection:

{'name' : 'abc-1','parent':'abc', 'price': 10}
{'name' : 'abc-2','parent':'abc', 'price': 5}
{'name' : 'abc-3','parent':'abc', 'price': 9}
{'name' : 'abc-4','parent':'abc', 'price': 11}

{'name' : 'efg', 'parent':'', 'price': 10}
{'name' : 'efg-1','parent':'efg', 'price': 5}
{'name' : 'abc-2','parent':'efg','price': 9}
{'name' : 'abc-3','parent':'efg','price': 11}

I want to perform following action:

a. Group By distinct parent
b. Sort all the groups based on price
c. For each group select a document with minimum price
  i. check each record's parent sku exists as a record in name field
  ii. If the name exists, do nothing
  iii. If the record does not exists, insert a document with parent as empty and other values as the  value of the record selected previously (minimum value).

I tired to do use for each as follows:

db.file.find().sort([("price", 1)]).forEach(function(doc){
          cnt = db.file.count({"sku": {"$eq": doc.parent}});
          if (cnt < 1){
               newdoc = doc;
               newdoc.name = doc.parent;
               newdoc.parent = "";
              delete newdoc["_id"];
              db.file.insertOne(newdoc);
          }
});

The problem with it is it takes too much time. What is wrong here? How can it be optimized? Would aggregation pipeline be a good solution, if yes how can it be done?

Answer 1

1. Retrieve a set of product names ✔

   def product_names():
       for product in db.file.aggregate([{$group: {_id: "$name"}}]):
           yield product['_id']
   
   
   product_names = set(product_names())
   

2. Retrieve product with minimum price from group ✔

   result_set = db.file.aggregate([
       {
           '$sort': {
               'price': 1,
           }
       }, 
       {
           '$group': {
               '_id': '$parent',
               'name': {
                   '$first': '$name',
               }, 
               'price': {
                   '$min': '$price',
               }
           }
       }, 
       {
           '$sort': {
               'price': 1,
           }
       }
   ])
   

3. Insert products retrieved in 2 if name not in set of product names retrieved in 1. ✔

   from pymongo.operations import InsertOne
   
   def insert_request(product):
       return InsertOne({
           name: product['name'],
           price: product['price'],
           parent: ''
       })
   
   requests = (
       insert_request(product)
       for product in result_set
       if product['name'] not in product_names
   )
   db.file.bulk_write(list(requests))
   

Steps 2 and 3 can be implemented in the aggregation pipeline.

db.file.aggregate([
    {
        '$sort': {'price': 1}
    }, 
    {
        '$group': {
            '_id': '$parent',
            'name': {
                '$first': '$name'
            }, 
            'price': {
                '$min': '$price'
            },
        }
    }, 
    {
        '$sort': {
            'price': 1
        }
    }, 
    {
        '$project': {
            'name': 1, 
            'price': 1,
            '_id': 0, 
            'parent':''
        }
    }, 
    {
        '$match': {
            'name': {
                '$nin': list(product_names())
            }
        }
    }, 
    {
        '$out': 'file'
    }
])