mongodb_queries.md

MongoDB Queries for media_clicks Collection

Database: admin (or your admin database name)

Collection: media_clicks

---

1. Find documents with ai_edit_last_date field

Find all documents that have ai_edit_last_date

db.media_clicks.find({ ai_edit_last_date: { $exists: true } })

Find documents with specific ai_edit_last_date

// Find by exact date (format: DD/MM/YYYY)
db.media_clicks.find({ ai_edit_last_date: "12/12/2025" })

Find documents with ai_edit_last_date in date range

// Find documents with dates in December 2025
db.media_clicks.find({ 
  ai_edit_last_date: { 
    $regex: /^\\d{2}\/12\/2025$/ 
  } 
})

Find documents with ai_edit_last_date NOT null/empty

db.media_clicks.find({ 
  ai_edit_last_date: { 
    $exists: true, 
    $ne: null, 
    $ne: "" 
  } 
})

---

2. Check if ai_edit_complete field exists

Find documents that HAVE ai_edit_complete field

db.media_clicks.find({ ai_edit_complete: { $exists: true } })

Find documents that DO NOT have ai_edit_complete field (old data)

db.media_clicks.find({ ai_edit_complete: { $exists: false } })

Find documents where ai_edit_complete is 0 (new users who haven't used model yet)

db.media_clicks.find({ ai_edit_complete: 0 })

Find documents where ai_edit_complete is greater than 0 (users who have used models)

db.media_clicks.find({ ai_edit_complete: { $gt: 0 } })

---

3. Combined Queries

Find documents with both fields

db.media_clicks.find({ 
  ai_edit_complete: { $exists: true },
  ai_edit_last_date: { $exists: true }
})

Find documents missing either field (old data)

db.media_clicks.find({ 
  $or: [
    { ai_edit_complete: { $exists: false } },
    { ai_edit_last_date: { $exists: false } }
  ]
})

Find users who have used models (ai_edit_complete > 0) with their last date

db.media_clicks.find({ 
  ai_edit_complete: { $gt: 0 },
  ai_edit_last_date: { $exists: true }
})

---

4. Count Queries

Count documents with ai_edit_last_date

db.media_clicks.countDocuments({ ai_edit_last_date: { $exists: true } })

Count documents with ai_edit_complete field

db.media_clicks.countDocuments({ ai_edit_complete: { $exists: true } })

Count documents without ai_edit_complete (old data)

db.media_clicks.countDocuments({ ai_edit_complete: { $exists: false } })

Count users who have used models (ai_edit_complete > 0)

db.media_clicks.countDocuments({ ai_edit_complete: { $gt: 0 } })

---

5. Aggregation Queries

Get statistics for ai_edit_complete

db.media_clicks.aggregate([
  {
    $group: {
      _id: null,
      total_users: { $sum: 1 },
      users_with_field: {
        $sum: { $cond: [{ $ifNull: ["$ai_edit_complete", false] }, 1, 0] }
      },
      users_without_field: {
        $sum: { $cond: [{ $ifNull: ["$ai_edit_complete", false] }, 0, 1] }
      },
      avg_ai_edit_complete: { $avg: "$ai_edit_complete" },
      max_ai_edit_complete: { $max: "$ai_edit_complete" },
      min_ai_edit_complete: { $min: "$ai_edit_complete" }
    }
  }
])

Get users by ai_edit_complete range

db.media_clicks.aggregate([
  {
    $group: {
      _id: {
        $switch: {
          branches: [
            { case: { $eq: ["$ai_edit_complete", null] }, then: "No field (old data)" },
            { case: { $eq: ["$ai_edit_complete", 0] }, then: "0 (new, not used)" },
            { case: { $lte: ["$ai_edit_complete", 5] }, then: "1-5 uses" },
            { case: { $lte: ["$ai_edit_complete", 10] }, then: "6-10 uses" },
            { case: { $gt: ["$ai_edit_complete", 10] }, then: "10+ uses" }
          ],
          default: "Unknown"
        }
      },
      count: { $sum: 1 }
    }
  },
  { $sort: { _id: 1 } }
])

Get latest ai_edit_last_date for each user

db.media_clicks.find(
  { ai_edit_last_date: { $exists: true } },
  { 
    userId: 1, 
    ai_edit_last_date: 1, 
    ai_edit_complete: 1 
  }
).sort({ ai_edit_last_date: -1 })

---

6. Update Queries (if needed to fix old data)

Add default ai_edit_complete = 0 to documents missing it

db.media_clicks.updateMany(
  { ai_edit_complete: { $exists: false } },
  { $set: { ai_edit_complete: 0 } }
)

Set ai_edit_last_date based on updatedAt for old documents

// Note: This converts updatedAt to DD/MM/YYYY format
// Run this carefully as it modifies data
db.media_clicks.find({ 
  ai_edit_last_date: { $exists: false },
  updatedAt: { $exists: true }
}).forEach(function(doc) {
  var date = doc.updatedAt;
  var formatted = date.getDate().toString().padStart(2, '0') + '/' + 
                  (date.getMonth() + 1).toString().padStart(2, '0') + '/' + 
                  date.getFullYear();
  db.media_clicks.updateOne(
    { _id: doc._id },
    { $set: { ai_edit_last_date: formatted } }
  );
})

---

7. Find by userId (ObjectId)

Find specific user by userId

// Replace with actual ObjectId
db.media_clicks.find({ 
  userId: ObjectId("693652b3f8683fd35b75448a") 
})

Find user and check if fields exist

db.media_clicks.findOne(
  { userId: ObjectId("693652b3f8683fd35b75448a") },
  { 
    userId: 1,
    ai_edit_complete: 1,
    ai_edit_last_date: 1,
    categories: 1,
    updatedAt: 1
  }
)

---

8. Find by Category

Find users who used specific category with ai_edit fields

db.media_clicks.find({
  "categories.categoryId": ObjectId("69368d62b95a6c2a75920505"),
  ai_edit_complete: { $exists: true }
})

---

Quick Test Queries

Check if field exists in sample document

db.media_clicks.findOne({}, { 
  userId: 1, 
  ai_edit_complete: 1, 
  ai_edit_last_date: 1 
})

Get sample of documents with and without fields

// With fields
db.media_clicks.find({ ai_edit_complete: { $exists: true } }).limit(5)

// Without fields (old data)
db.media_clicks.find({ ai_edit_complete: { $exists: false } }).limit(5)