MongoDB数据模型设计与聚合查询问题求助

Hey there! I’ve worked on a couple of game automation bot projects before, so I get exactly what you’re trying to build. Let’s walk through how to model your data and set up the queries you need.

1. Data Model Design #

First, let’s nail down the document structure. You have two core needs: full transaction history tracking, and easy access to the latest valid state for each bot. Here are two solid approaches:

Option 1: Embed State Snapshots in Transaction Logs #

This keeps everything in one collection, which simplifies queries since you don’t need to join across collections. Each document represents a single bot action, plus the state of the bot right after that action:

{
  _id: ObjectId("..."),
  botId: "bot_001", // Unique ID for your bot
  operationType: "trade", // e.g., "collect", "craft", "trade"
  transactionDetails: {
    item: "gold_coin",
    quantity: 50,
    target: "merchant_npc_01",
    success: true
  },
  timestamp: ISODate("2024-05-20T14:30:00Z"), // Critical for date range queries
  currentState: { // Snapshot of the bot's state post-operation
    inventory: { gold_coin: 120, health_potion: 3 },
    position: { x: 45, y: 12 },
    status: "active"
  }
}

• Pros: No need for separate state collections; querying the latest state is just grabbing the most recent document for a bot.
• Cons: Slightly more storage usage since you’re duplicating state data, but for most small-scale bot projects, this is negligible.

Option 2: Separate Transaction Logs & State Collections #

If you want to save storage (or have very large state objects), split into two collections:

• bot_transactions: Stores all historical actions, no state snapshots:

  {
    _id: ObjectId("..."),
    botId: "bot_001",
    operationType: "trade",
    transactionDetails: { /* ... */ },
    timestamp: ISODate("2024-05-20T14:30:00Z")
  }
  

• bot_states: Only keeps the latest valid state for each bot (update this every time a bot completes an action):

  {
    _id: ObjectId("..."),
    botId: "bot_001",
    lastUpdated: ISODate("2024-05-20T14:30:00Z"),
    currentState: { /* ... */ }
  }
  

• Pros: Saves storage by avoiding duplicate state data.
• Cons: Requires two write operations per bot action (one for the transaction, one to update the state).

2. Essential Queries #

Now let’s cover the queries you need, using Option 1 as the example (adjustments for Option 2 are noted):

Query All Transactions for a Bot (or All Bots) #

To get every transaction in the system:

db.bot_transactions.find({})

To filter by a specific bot:

db.bot_transactions.find({ botId: "bot_001" })

Query Transactions by Date Range #

Use the timestamp field with $gte and $lte to narrow down to a specific window:

// Get transactions between May 1st and May 20th, 2024
db.bot_transactions.find({
  timestamp: {
    $gte: ISODate("2024-05-01T00:00:00Z"),
    $lte: ISODate("2024-05-20T23:59:59Z")
  }
})
// Add botId filter if you want a specific bot's date-range transactions
db.bot_transactions.find({
  botId: "bot_001",
  timestamp: { $gte: ISODate("2024-05-01T00:00:00Z"), $lte: ISODate("2024-05-20T23:59:59Z") }
})

Get the Latest Valid State for a Bot #

If using Option 1 (state embedded in transactions), sort by timestamp descending and grab the first result:

db.bot_transactions.findOne(
  { botId: "bot_001", "transactionDetails.success": true }, // Filter for successful/valid actions
  { sort: { timestamp: -1 } }
)

If using Option 2, just query the bot_states collection for the bot:

db.bot_states.findOne({ botId: "bot_001" })

3. Optimization Tips #

• Indexes: Create indexes on fields you query often to speed things up:

  // For bot-specific and date-range queries
  db.bot_transactions.createIndex({ botId: 1, timestamp: -1 })
  // For global date-range queries
  db.bot_transactions.createIndex({ timestamp: -1 })
  

• Manual Cleanup: To delete old transactions efficiently, use the timestamp index with deleteMany:

  // Delete all transactions older than 90 days
  db.bot_transactions.deleteMany({
    timestamp: { $lt: ISODate(new Date(Date.now() - 90 * 24 * 60 * 60 * 1000)) }
  })
  

  Run this command manually on a schedule that works for your project.

Final Notes #

For a small-scale bot project, Option 1 is usually the easiest to implement and maintain—you avoid the complexity of managing two collections and cross-referencing data. The slight storage tradeoff is worth the simplicity.

内容的提问来源于stack exchange，提问作者Exio4