Mongo Queries

Mongo Queries

Admin Queries

CRUD Queries:

# Update all document in a collection satisfying a condition of attribute in that collection.
Eg: add {"partner":true} in a collection provider if customers array contains one or more element.
>>>   db.provider.update({"customers": {"$ne": [], "$exists": true}}, {"$set": {"partner": true}}, {"multi": true})



# monthly counts of all records
>>>
db.ping.aggregate([
  {
    $group : 
    {
   _id: { month: { $month: "$created_date" }, year: { $year: "$created_date" }},
   count: { $sum: 1 }
    }
  }
]);



# Distinct query:
>>> db.provider.distinct('region');



# Distinct multiple attribute query:
>>>
db.provider.aggregate([
  {"$group" : {"_id" : {region_code: "$region_code" , region: "$region"}}}
]);



# Flatened Query:
db.region.aggregate (
{ $project: {"code":1, "description":1, "metro.code":1,  "metro.description":1 , "metro.ibx.name":1,  "metro.ibx.description":1, _id:0}},
{ $unwind: '$metro'},
{ $unwind: '$metro.ibx'}
);


# empty & null check
1. {"description" : {"$exists" : true, "$ne" : ""}}
2. {$and: [{description : {$exists : true}}, {description : {$ne : ""}}, {description : {$ne : null }}]}
3. {"$and": [{"description" : {"$exists" : true}}, {"description" : {"$ne" : ""}}, {"description" : {"$ne" : null }}]}

# Array Empty Check
1. { pictures: { $exists: true, $not: {$size: 0} } }
2. { pictures: { $exists: true, $ne: [] } } /* mongodb don't use index if $size is involved */
3. { pictures: { $gt: [] } }

# Regex Queries:
{$or : [{ibx_a: {$regex: /FR/}}, {ibx_b: {$regex: /FR/}}]}

# Persa Ibx:
{"manual_provider_id": {"$ne": null}, "accounts.ibxs.ibx_code" : {"$in": ['RJ1', 'RJ2', 'RJ3', 'SP1', 'SP2', 'SP3', 'SP4']}}

# find duplicate records whose count is greater than 1:
db.provider.aggregate(
    {"$group" : { "_id": "$provider_account_name", "status":{$first:"$status"}, "manual_provider_id": {"$first" : "$manual_provider_id"},"parent_sfdc_id": {"$first" : "$parent_sfdc_id"}, "count": { "$sum": 1 } } },
    {"$match": {"_id" :{ "$ne" : null } , "count" : {"$gt": 1}, "status" : {"$eq": "ACTIVE"}, "manual_provider_id": {"$eq" : null} } },
    {"$project": {"name" : "$_id", "_id" : 0} }
)

# total count
db.provider.aggregate(
    {"$group" : { "_id": "$provider_account_name", "status":{$first:"$status"}, "count": { "$sum": 1 } } },
    {"$match": {"_id" :{ "$ne" : null } , "count" : {"$gt": 1}, "status" : {"$eq": "ACTIVE"} } },
    { $group: { _id: null, count: { $sum: 1 } } }
)

db.provider.aggregate(
    {"$group" : { "_id": "$provider_account_name", "status":{$first:"$status"}, "manual_provider_id": {"$first" : "$manual_provider_id"},"parent_sfdc_id": {"$first" : "$parent_sfdc_id"}, "count": { "$sum": 1 } } },
    {"$match": {"_id" :{ "$ne" : null } , "count" : {"$gt": 1}, "status" : {"$eq": "ACTIVE"}, "manual_provider_id": {"$eq" : null} } }
)