learn

PHOTO

Mon Oct 14 2024 12:29:45 GMT+0000 (Coordinated Universal Time)

Saved by @CodeWithSachin #aggregation #mongodb #$objecttoarray #$addfiels

[
  {
    $project: {
      _id: 1,
      followUps: 1,
      duplicateNames: {
        $filter: {
          input: {
            $map: {
              input: "$followUps",
              as: "f",
              in: "$$f.name"
            }
          },
          as: "name",
          cond: {
            $and: [
              { $ne: ["$$name", "Check_in"] }, 
              {
                $gt: [
                  { $size: {
                      $filter: {
                        input: {
                          $filter: {
                            input: "$followUps",
                            as: "innerF",
                            cond: { $eq: ["$$innerF.name", "$$name"] }
                          }
                        },
                        as: "duplicate",
                        cond: { $eq: ["$$duplicate.name", "$$name"] }
                      }
                    }
                  },
                  1
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: { $gt: [{ $size: "$duplicateNames" }, 0] }
    }
  },
  {
    $project: {
      _id: 1
    }
  }
]

COPY

Save snippets that work from anywhere online with our extensions

Comments

More like this

#javascript #mongoose #mongodb #mean

mongodb - How to find items using regex in Mongoose for partial text search

   var Person = mongoose.model('Person', yourSchema);
   // find each person with a name contains 'Ghost'
   Person.findOne({ "name" : { $regex: /Ghost/, $options: 'i' } },
          function (err, person) {
                 if (err) return handleError(err);
                 console.log('%s %s is a %s.', person.name.first, person.name.last, person.occupation);

   });

#nodejs #mongoose #mongodb

Split the customerName into firstName..the fisrt letter..the first index

db.customers.updateMany({},
  [{ $set : { m : {$arrayElemAt:[{ $split: ["$customerName", " "] },0]}} }],

);

#aggregation #mongodb #$objecttoarray #$addfiels

$objectToArray with $addFiels

[
    {
        "$match": {
            "challengeId": ObjectId("6674404413958543b594fe6f")
        }
    },
    {
        "$project": {
            "email": "$registrationDetails.email",
            "points": "$overAllImpact.point",
            "WeightImpact": "$overAllImpact.overAllWeightImpact",
            "eventPoints": "$eventPoint",
            "weeklyWeightLog": {
                "$objectToArray": "$weeklyWeightLog"
            }
        }
    },
    {
        "$unwind": "$weeklyWeightLog"
    },
    {
        "$addFields": {
            "weeklyWeightLog.kDate": {
                "$toDate": "$weeklyWeightLog.k"
            }
        }
    },
    {
        "$sort": {
            "weeklyWeightLog.kDate": -1
        }
    }
]

#aggregation #mongodb #$objecttoarray #$addfiels

broadcast users on app (query)

[
    {
        "$lookup": {
            "from": "participant",
            "localField": "participantId",
            "foreignField": "_id",
            "as": "participants"
        }
    },
    {
        "$unwind": {
            "path": "$participants",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$lookup": {
            "from": "subscription",
            "localField": "participantId",
            "foreignField": "userId",
            "as": "subscriptions"
        }
    },
    {
        "$unwind": {
            "path": "$subscriptions",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$lookup": {
            "from": "subscriptionPlan",
            "localField": "subscriptions.planId",
            "foreignField": "_id",
            "as": "subscriptionPlans"
        }
    },
    {
        "$unwind": {
            "path": "$subscriptionPlans",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$match": {
            "nutritionistID": {
                "$oid": "64782249defe1c75d7689cbd"
            }
        }
    },
    {
        "$match": {
            "subscriptions.planId": {
                "$exists": true
            }
        }
    },
    {
        "$match": {
            "participants": {
                "$exists": true
            }
        }
    },
    {
        "$match": {
            "participants": {
                "$exists": true
            }
        }
    },
    {
        "$match": {
            "active": true
        }
    }
]




//query 2
[
    {
        "$lookup": {
            "from": "participant",
            "localField": "participantId",
            "foreignField": "_id",
            "as": "participants"
        }
    },
    {
        "$unwind": {
            "path": "$participants",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$lookup": {
            "from": "subscription",
            "localField": "participantId",
            "foreignField": "userId",
            "as": "subscriptions"
        }
    },
    {
        "$unwind": {
            "path": "$subscriptions",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$lookup": {
            "from": "subscriptionPlan",
            "localField": "subscriptions.planId",
            "foreignField": "_id",
            "as": "subscriptionPlans"
        }
    },
    {
        "$unwind": {
            "path": "$subscriptionPlans",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$match": {
            "nutritionistID": {
                "$oid": "64782249defe1c75d7689cbd"
            }
        }
    },
    {
        "$match": {
            "subscriptions.planId": {
                "$exists": false
            }
        }
    },
    {
        "$match": {
            "participants": {
                "$exists": true
            }
        }
    },
    {
        "$match": {
            "participants": {
                "$exists": true
            }
        }
    },
    {
        "$match": {
            "active": true
        }
    }
]



for tavishi table is -> userNutrionistMapping

#aggregation #mongodb #$objecttoarray #$addfiels

learn

[
  {
    $project: {
      _id: 1,
      followUps: 1,
      duplicateNames: {
        $filter: {
          input: {
            $map: {
              input: "$followUps",
              as: "f",
              in: "$$f.name"
            }
          },
          as: "name",
          cond: {
            $and: [
              { $ne: ["$$name", "Check_in"] }, 
              {
                $gt: [
                  { $size: {
                      $filter: {
                        input: {
                          $filter: {
                            input: "$followUps",
                            as: "innerF",
                            cond: { $eq: ["$$innerF.name", "$$name"] }
                          }
                        },
                        as: "duplicate",
                        cond: { $eq: ["$$duplicate.name", "$$name"] }
                      }
                    }
                  },
                  1
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: { $gt: [{ $size: "$duplicateNames" }, 0] }
    }
  },
  {
    $project: {
      _id: 1
    }
  }
]

#aggregation #mongodb #pagination

MongoDB aggregation for pagination

db.Order.aggregate([
    { '$match'    : { "company_id" : ObjectId("54c0...") } },
    { '$sort'     : { 'order_number' : -1 } },
    { '$facet'    : {
        metadata: [ { $count: "total" }, { $addFields: { page: NumberInt(3) } } ],
        data: [ { $skip: 20 }, { $limit: 10 } ] // add projection here wish you re-shape the docs
    } }
] )

##jupyter #aggregation

Dashboarad->script

# 1- > Deleting based on _id in baselineTable

from bson import ObjectId
import json

file_path = 'delete_ids'

with open(file_path, 'r') as file:
    data = json.load(file)

data_array = data['data']

delete_object_ids = [ObjectId(item) for item in data_array]
db["participantBaselineAndFollowupData"].delete_many({"_id": {"$in": delete_object_ids}})
#to check
# records = db["participantBaselineAndFollowupData"].find({"_id": {"$in": delete_object_ids}})
# count=0
# for record in records:
#     count=count+1
#     print(record["_id"])
# print(count)

##jupyter #aggregation

Dashboard script

# 2-> 1 subs 1 baseline
import json

file_path = "referOneSub"

with open(file_path, 'r') as file:
    data = json.load(file)

data_array = data['data']
insert_object_ids = [ObjectId(_id) for _id in data_array]
print(len(insert_object_ids))
# print(insert_object_ids)

from bson.objectid import ObjectId

for _id in insert_object_ids:
#     print("one")
    record = db["participantBaselineAndFollowupData"].find_one({"_id": _id})
    
    if record:
        user_id = record["participantId"]
        
        subscription = db["subscription"].find_one({"userId": user_id})
        
        if subscription:
            program_start_date = subscription.get("startDate", "")
            program_code = subscription["subscriptionPlan"].get("programCode", "")
            
            db["participantBaselineAndFollowupData"].update_one(
                {"_id": _id},
                {"$set": {"programCode": program_code, "programStartDate": program_start_date}}
            )
print("completed")

##jupyter #aggregation

Dashboard script

#3-> one baseline 2 subs refer the given program code
import json

file_path = "oneBase2Sub"
try:
    with open(file_path, "r") as file:
        data1 = json.load(file)
        # print(data)

    data_list = list(data1.items())
    # print(data_list)
except Exception as e:
    print("Error:", e)


print(len(data_list))

for data_tuple in data_list:
    _id, program_code = data_tuple

    participant_record = db["participantBaselineAndFollowupData"].find_one({"_id": ObjectId(_id)})

    if participant_record:
        user_id = participant_record["participantId"]

        subscription_record = db["subscription"].find_one({"userId": user_id, "subscriptionPlan.programCode": program_code})
        
        if subscription_record:
            program_start_date = subscription_record.get("startDate", "")
            program_code_from_subscription = subscription_record["subscriptionPlan"].get("programCode", "")
#             print(program_start_date)
#             print(program_code_from_subscription)
            db["participantBaselineAndFollowupData"].update_one(
                {"_id": participant_record["_id"]},
                {"$set": {"programCode": program_code, "programStartDate": program_start_date}}
            )
print("Completed")

##jupyter #aggregation

jupyter query 2

# 2-> 1 subs 1 baseline
import json

file_path = "referOneSub"

with open(file_path, 'r') as file:
    data = json.load(file)

data_array = data['data']
insert_object_ids = [ObjectId(_id) for _id in data_array]
print(len(insert_object_ids))
# print(insert_object_ids)




from bson.objectid import ObjectId

not_found_ids = []
not_updated_ids = []

for _id in insert_object_ids:
    record = db["participantBaselineAndFollowupData"].find_one({"_id": _id})
    
    if record:
        user_id = record["participantId"]
        
        subscription = db["subscription"].find_one({"userId": user_id})
        
        if subscription:
            program_start_date = subscription.get("startDate", "")
            program_code = subscription["subscriptionPlan"].get("programCode", "")
            
            result = db["participantBaselineAndFollowupData"].update_one(
                {"_id": _id},
                {"$set": {"programCode": program_code, "programStartDate": program_start_date}}
            )
            
            if result.modified_count == 0:
                not_updated_ids.append(_id)
        else:
            not_updated_ids.append(_id)
    else:
        not_found_ids.append(_id)

print("Completed")

if not_found_ids:
    print(f"IDs not found: {print(not_found_ids)}")

if not_updated_ids:
    print(f"IDs not updated: {len(not_updated_ids)}")
    print(f"IDs not updated: {not_updated_ids}")

##jupyter #aggregation

jupyter 3

#3-> one baseline 2 subs refer the given program code
import json

file_path = "oneBase2Sub"
try:
    with open(file_path, "r") as file:
        data1 = json.load(file)
        # print(data)

    data_list = list(data1.items())
    # print(data_list)
except Exception as e:
    print("Error:", e)


print(len(data_list))









from bson.objectid import ObjectId

not_found_ids = []
not_updated_ids = []

for data_tuple in data_list:
    _id, program_code = data_tuple

    participant_record = db["participantBaselineAndFollowupData"].find_one({"_id": ObjectId(_id)})

    if participant_record:
        user_id = participant_record["participantId"]

        subscription_record = db["subscription"].find_one({"userId": user_id, "subscriptionPlan.programCode": program_code})
        
        if subscription_record:
            program_start_date = subscription_record.get("startDate", "")
            program_code_from_subscription = subscription_record["subscriptionPlan"].get("programCode", "")
            
            result = db["participantBaselineAndFollowupData"].update_one(
                {"_id": participant_record["_id"]},
                {"$set": {"programCode": program_code, "programStartDate": program_start_date}}
            )
            
            if result.modified_count == 0:
                not_updated_ids.append(_id)
        else:
            not_updated_ids.append(_id)
    else:
        not_found_ids.append(_id)

print("Completed")


if not_found_ids:
    print(f"IDs not found: {len(not_found_ids)}")
    print(f"IDs not found: {not_found_ids}")

if not_updated_ids:
    print(f"IDs not updated: {len(not_updated_ids)}")
    print(f"IDs not updated: {not_updated_ids}")

##jupyter #aggregation

jupyter 4

#4-> Refer Subscription based on date.(2 subscription)

import json
from datetime import datetime
from bson.objectid import ObjectId

with open('referSubByDate.json', 'r') as file:
    data = json.load(file)

not_found_ids = []
not_updated_ids = []

for item in data['data']:
    _id = item['id']
    date = item['date']
    date_object = datetime.strptime(date, "%d/%m/%Y")
    new_date = date_object.strftime("%Y-%m-%dT%H:%M:%S.000+00:00")
    
    participant_record = db["participantBaselineAndFollowupData"].find_one({"_id": ObjectId(_id)})
    
    if participant_record:
        user_id = participant_record["participantId"]
        
        subscription_record = db["subscription"].find_one({"userId": user_id, "startDate": new_date})
        
        if subscription_record:
            startDate = subscription_record["startDate"]
            program_code = subscription_record["subscriptionPlan"].get("programCode")
            
            result = db["participantBaselineAndFollowupData"].update_one(
                {"_id": participant_record["_id"]},
                {"$set": {"programCode": program_code, "programStartDate": startDate}}
            )
            
            if result.modified_count == 0:
                not_updated_ids.append(_id)
        else:
            not_updated_ids.append(_id)
    else:
        not_found_ids.append(_id)

print("Completed")

if not_found_ids:
    print(f"IDs not found: {not_found_ids}")

if not_updated_ids:
    print(f"IDs not updated: {not_updated_ids}")

##jupyter #aggregation

jupyter 1

# 1- > Deleting based on _id in baselineTable

from bson import ObjectId
import json

file_path = 'delete_ids'

with open(file_path, 'r') as file:
    data = json.load(file)

data_array = data['data']

delete_object_ids = [ObjectId(item) for item in data_array]
db["participantBaselineAndFollowupData"].delete_many({"_id": {"$in": delete_object_ids}})
#to check
# records = db["participantBaselineAndFollowupData"].find({"_id": {"$in": delete_object_ids}})
# count=0
# for record in records:
#     count=count+1
#     print(record["_id"])
# print(count)

##jupyter #aggregation

Metabase date

[
    {
        "$match": {
            "userId": 
                ObjectId("64d1ee7758a82e63a46206fe")
            ,
            "$and": [
                {
                    "insertedFor": {
                        "$gte": {
                            "$date": "2024-02-18T09:52:03Z"
                        }
                    }
                },
                {
                    "insertedFor": {
                        "$lte": {
                            "$date": "2024-02-20T09:52:03Z"
                        }
                    }
                }
            ]
        }
    },
    {
        "$project": {
            "insertedFor": 1
        }
    },
  {
    $count:"toot"
  }
]

##jupyter #aggregation #mongodb

Mongo $ne and $ifNull while projection

[
    {
      "$lookup": {
        "from": "Assessment",
        "localField": "assessmentId",
        "foreignField": "_id",
        "as": "assessment"
      }
    },
    {
      "$unwind": {
        "path": "$assessment",
        "preserveNullAndEmptyArrays": true
      }
    },
    {
      "$match": {
        "$and": [
          {
            "assessmentId": {
              "$oid": "622072b1aaeb4e5955319304"
            },
            "endCode": {
              "$ne": ""
            }
          }
        ]
      }
    },
   {
  "$project": {
    "_id": 1,
    "name": {
      "$cond": {
        "if": { "$ifNull": ["$assessmentType", false] },
        "then": "$assessmentType",
        "else": "IDRS"
      }
    },
    "assessmentId": "$_id",
    "ok": "$assessment.type",
    "createdAt": 1
  }
}
,
   {
      "$sort": {
        "createdAt": -1
      }
    }

  ]

learn

Save snippets that work from anywhere online with our extensions

Comments

More like this

Browse more snippets >>

Embed code snippet