MongoDB: Noções básicas sobre consultas a documentos

// ❌ Counterexample:Return all fields
app.get('/api/products', async (req, res) => {
  const products = await Product.find();  // Return all fields
  res.json(products);
});
// Each product 5KB，100 items = 500KB
// Slow Internet Connection + Slow front-end parsing

// ✅ Correct Example:Return only the necessary fields
app.get('/api/products', async (req, res) => {
  const products = await Product.find(
    { isActive: true },
    {
      projection: {
        sku: 1,
        title: 1,
        price: 1,
        thumbnail: 1,
        _id: 0   // Exclusion _id
      }
    }
  )
    .sort({ createdAt: -1 })
    .limit(20)
    .lean();   // Skip mongoose hydrate，Performance ↑3-5x

  res.json(products);
});
// Each product 200 Byte，20 items = 4KB(Performance ↑100x)

// === find Basic Syntax ===
db.products.find();
// Back to All Documents(cursor)

// === find Specified Conditions ===
db.products.find({ category: "Electronics" });
// Back to All Electronics

// === find Return an array ===
db.products.find({ category: "Electronics" }).toArray();
// Back Array<Document>

// === find Iterate(Cursor)===
db.products.find({ category: "Electronics" }).forEach(printjson);

// === findOne Return to a single document ===
db.products.findOne({ sku: "PHONE-001" });
// Return the first matching document(or null)

// === findOne vs find().limit(1) Difference ===
const doc1 = db.products.findOne({ sku: "PHONE-001" });
const doc2 = db.products.find({ sku: "PHONE-001" }).limit(1).next();
// The results are the same，findOne More concise

// === mongoose: find returns an array ===
const products = await Product.find({ category: "Electronics" });
// Array<Product>

// === mongoose: findOne returns an object ===
const product = await Product.findOne({ sku: "PHONE-001" });
// Product | null

// === Handling Cases Where Query Results Are Empty ===
const product = await Product.findOne({ sku: "NOT_EXIST" });
if (!product) {
  return res.status(404).json({ error: "Product not found" });
}

// === Search in mongosh ===
// Search All Documents
db.products.find();

// Query by Specified Criteria
db.products.find({ category: "Electronics" });

// Multi-Condition Query(AND)
db.products.find({
  category: "Electronics",
  stock: { $gt: 0 }     // Inventory greater than 0
});

// Look Up and Format
db.products.find({ category: "Electronics" }).pretty();

// Query and Count
db.products.find({ category: "Electronics" }).count();

// === Search in Node.js ===
const { MongoClient } = require('mongodb');

async function findProducts() {
  const client = new MongoClient('mongodb://localhost:27017');
  await client.connect();
  const collection = client.db('shopdb').collection('products');

  // 1. find() Back Cursor
  const cursor = collection.find({ category: "Electronics" });
  const products = await cursor.toArray();
  console.log(`Found ${products.length} products`);

  // 2. findOne() Back Document
  const product = await collection.findOne({ sku: "PHONE-001" });
  console.log(product);

  // 3. Iterate Cursor(Flow)
  for await (const doc of collection.find({ category: "Electronics" })) {
    console.log(doc.title);
  }

  await client.close();
}

findProducts();

// === Exact Match ===
db.products.find({ sku: "PHONE-001" });

// === Multiple conditions AND ===
db.products.find({
  category: "Electronics",
  stock: 50,
  isActive: true
});

// === Field does not exist ===
db.products.find({ discount: { $exists: false } });

// === Nested Document Query ===
db.products.find({ "specs.battery": "4500mAh" });

// === Array Element Matching ===
db.products.find({ tags: "5g" });

// === Matching Multiple Elements in an Array ===
db.products.find({ tags: { $all: ["5g", "amoled"] } });

// === $eq(equals，Default)===
db.products.find({ price: { $eq: 599.99 } });
// equivalent to { price: 599.99 }

// === $ne(is not equal to)===
db.products.find({ category: { $ne: "Books" } });

// === $gt / $gte(greater than / Greater than or equal to)===
db.products.find({ price: { $gt: 100 } });        // > 100
db.products.find({ price: { $gte: 100 } });       // >= 100

// === $lt / $lte(Less than / Less than or equal to)===
db.products.find({ price: { $lt: 1000 } });
db.products.find({ price: { $lte: 1000 } });

// === $in / $nin(Includes / Excludes)===
db.products.find({ category: { $in: ["Electronics", "Books"] } });
db.products.find({ category: { $nin: ["Clothing"] } });

// === Range Query ===
db.products.find({
  price: { $gte: 100, $lte: 1000 }  // 100 <= price <= 1000
});

// === $and(Implicit AND)===
db.products.find({
  category: "Electronics",
  stock: { $gt: 0 }    // Implicit AND
});

// === $and(Explicit AND)===
db.products.find({
  $and: [
    { category: "Electronics" },
    { $or: [{ stock: { $gt: 10 } }, { isFeatured: true }] }
  ]
});

// === $or ===
db.products.find({
  $or: [
    { category: "Electronics" },
    { tags: "bestseller" }
  ]
});

// === $not ===
db.products.find({ price: { $not: { $gt: 1000 } } });
// Price <= 1000

// === $nor(None of them match)===
db.products.find({
  $nor: [
    { category: "Electronics" },
    { category: "Books" }
  ]
});

// === Scene:Check Prices 100-1000、Inventory greater than 0、Electronics or Books Categorized Products ===
db.products.find({
  price: { $gte: 100, $lte: 1000 },
  stock: { $gt: 0 },
  $or: [
    { category: "Electronics" },
    { category: "Books" }
  ],
  isActive: true
}).sort({ price: 1 }).limit(20);

// === mongoose Equivalent Notation ===
const products = await Product.find({
  price: { $gte: 100, $lte: 1000 },
  stock: { $gt: 0 },
  $or: [{ category: "Electronics" }, { category: "Books" }],
  isActive: true
})
  .sort({ price: 1 })
  .limit(20)
  .lean();

// === Field Whitelist(Return only the specified fields)===
db.products.find(
  { category: "Electronics" },
  { sku: 1, title: 1, price: 1 }
);
// Back:{ _id, sku, title, price }

// === _id Return by Default，Must be explicitly excluded ===
db.products.find(
  {},
  { sku: 1, title: 1, _id: 0 }  // _id: 0 Exclusion _id
);

// === Field Blacklist(Exclude Specified Fields)===
db.products.find(
  {},
  { internalNotes: 0, debugInfo: 0 }  // Exclude Sensitive Fields
);

// === Nested Document Projection ===
db.products.find(
  { sku: "PHONE-001" },
  {
    sku: 1,
    title: 1,
    "specs.screen": 1,    // Return only specs.screen
    "specs.battery": 1   // Return only specs.battery
  }
);

// === Array Element Projection($slice)===
db.reviews.find(
  { productId: "PHONE-001" },
  {
    title: 1,
    content: 1,
    comments: { $slice: 3 }  // Return only the first 3 Comments
  }
);

// === Performance Testing:100 10,000 Documents，Search 100 items ===

// ❌ No projection:Back 5MB
db.products.find({ category: "Electronics" }).limit(100);
// Time taken 800ms

// ✅ Projection available:Back 200KB
db.products.find(
  { category: "Electronics" },
  { sku: 1, title: 1, price: 1, _id: 0 }
).limit(100);
// Time taken 80ms(Performance ↑10x)

// === Methods 1:projection option ===
const products = await Product.find({ category: "Electronics" }, "sku title price");
// String Syntax(Space-separated)

// === Methods 2:select() Chain-type ===
const products = await Product.find()
  .select("sku title price")
  .select("-description -images");  // Exclude certain fields

// === Methods 3:Object Syntax ===
const products = await Product.find(
  { category: "Electronics" },
  { sku: 1, title: 1, price: 1, _id: 0 }
);

// === Methods 4:lean() + select() Optimal Performance ===
const products = await Product.find()
  .select("sku title price")
  .lean()  // Skip mongoose hydrate
  .limit(100);

// === Complete List of E-commerce Sites API ===
app.get('/api/products', async (req, res) => {
  const {
    category,
    minPrice,
    maxPrice,
    search,
    sort = 'createdAt',
    order = 'desc',
    page = 1,
    limit = 20
  } = req.query;

  // 1. Build Query Criteria
  const query = { isActive: true };
  if (category) query.category = category;
  if (minPrice || maxPrice) {
    query.price = {};
    if (minPrice) query.price.$gte = NumberDecimal(minPrice);
    if (maxPrice) query.price.$lte = NumberDecimal(maxPrice);
  }
  if (search) query.title = new RegExp(search, 'i');

  // 2. Sort
  const sortObj = { [sort]: order === 'desc' ? -1 : 1 };

  // 3. Pagination
  const skip = (page - 1) * limit;

  // 4. Search(With Projector + lean)
  const products = await Product.find(query)
    .select('sku title price thumbnail rating reviewCount')  // Return only 6 field
    .sort(sortObj)
    .skip(skip)
    .limit(Number(limit))
    .lean();   // Key:Skip mongoose hydrate

  // 5. Total Count
  const total = await Product.countDocuments(query);

  res.json({
    products,
    pagination: {
      page: Number(page),
      limit: Number(limit),
      total,
      pages: Math.ceil(total / limit)
    }
  });
});

// === Default Output (compact)===
db.products.findOne({ sku: "PHONE-001" });
// { _id: ObjectId('...'), sku: 'PHONE-001', title: 'Phone', ... }

// === pretty() Format ===
db.products.findOne({ sku: "PHONE-001" }).pretty();
// {
//   _id: ObjectId('507f1f77bcf86cd799439011'),
//   sku: 'PHONE-001',
//   title: 'Smartphone X',
//   price: NumberDecimal('599.99'),
//   ...
// }

// === find Also supported pretty ===
db.products.find({ category: "Electronics" }).pretty();

# pretty Active in interactive mode，No differences in the script output
mongosh "mongodb://localhost:27017" --eval "db.products.find().pretty()"

// === Usage printjson() ===
db.products.find().forEach(printjson);
// Output the complete JSON Structure

// === Usage tojson() ===
const doc = db.products.findOne();
print(tojson(doc));

// === Format the output(pretty 2)===
printjson(doc, null, 2);

// === Back 10 items ===
db.products.find().limit(10);

// === Conditions for Cooperation ===
db.products.find({ category: "Electronics" }).limit(5);

// === limit(0) equivalent to limit(1) ===
db.products.find().limit(0);  // Back 1 items

// === limit(-1) Return All (Special)===
db.products.find().limit(-1);  // Back to All(Used as a reverse sort)

// === Skip to the beginning 10 items，Back to Page 11-20 items ===
db.products.find().skip(10).limit(10);

// === Page Numbering Formula ===
// Page N(per page 20 items):skip = (N - 1) * 20
db.products.find().skip((page - 1) * 20).limit(20);

// === skip + sort Consistency ===
db.products.find().sort({ _id: 1 }).skip(10).limit(10);

// === Ascending (1)===
db.products.find().sort({ price: 1 });     // Price (ascending)

// === Descending (-1)===
db.products.find().sort({ createdAt: -1 }); // Latest First

// === Sorting by Multiple Fields ===
db.products.find().sort({ category: 1, price: -1 });
// Press first category ascending，Press again price descending

// === Sorting Nested Fields ===
db.products.find().sort({ "specs.rating": -1 });

// === Sorting Array Fields ===
db.products.find().sort({ "tags.0": 1 });  // by tags Sorting the First Element

// === Full Paginated Query ===
db.products
  .find({ category: "Electronics", isActive: true })
  .sort({ price: 1, createdAt: -1 })  // Price (ascending)，Reverse Chronological Order
  .skip(20)                            // Skip 20 items
  .limit(10);                          // Back 10 items

// === mongoose Equivalent Notation ===
const products = await Product
  .find({ category: "Electronics", isActive: true })
  .sort({ price: 1, createdAt: -1 })
  .skip(20)
  .limit(10)
  .lean();

// === Traditional Pagination(Slow page turns)===
const page1 = await Product.find().skip(0).limit(20);
const page1000 = await Product.find().skip(20000).limit(20);  // Slow!

// === Cursor-Based Pagination(Recommendations)===
const lastId = null;  // The First Time
const products1 = await Product.find({ _id: { $gt: lastId } }).limit(20);

const nextLastId = products1[products1.length - 1]._id;
const products2 = await Product.find({ _id: { $gt: nextLastId } }).limit(20);
// Stable performance，Not affected by page depth

// === Comprehensive Practical Training:Pagination of Product List API ===
app.get('/api/products', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const sortBy = req.query.sort || 'createdAt';
  const order = req.query.order === 'asc' ? 1 : -1;

  const products = await Product.find({ isActive: true })
    .select('sku title price thumbnail rating')
    .sort({ [sortBy]: order })
    .skip((page - 1) * limit)
    .limit(limit)
    .lean();

  const total = await Product.countDocuments({ isActive: true });

  res.json({
    data: products,
    pagination: {
      page,
      limit,
      total,
      pages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1
    }
  });
});

// === Count all documents ===
db.products.countDocuments();
// 1250

// === Statistics Meet the Criteria ===
db.products.countDocuments({ category: "Electronics" });
// 250

// === With options ===
db.products.countDocuments(
  { category: "Electronics" },
  { limit: 1000 }   // Most Scans 1000 items
);

// === mongoose Equivalent ===
const count = await Product.countDocuments({ category: "Electronics" });
// 250

// === Estimated Total(Metadata-Based，Extremely fast)===
db.products.estimatedDocumentCount();
// 1250(Approximate value)

// === Applicable Scenarios ===
// - List Page Display"Total 1000 items"(No need for precision)
// - Statistics that do not require real-time processing

// === Performance Comparison ===
// countDocuments({}): ~100ms(Full Table Scan)
// estimatedDocumentCount(): ~1ms(Read Metadata)

// === Product Category Statistics(Accurate)===
const stats = await Product.aggregate([
  { $group: { _id: "$category", count: { $sum: 1 } } },
  { $sort: { count: -1 } }
]);
// [
//   { _id: 'Electronics', count: 250 },
//   { _id: 'Books', count: 200 },
//   { _id: 'Clothing', count: 180 }
// ]

// === Total Number of List Pages(Estimate)===
const totalProducts = await Product.estimatedDocumentCount();
const electronicsCount = await Product.countDocuments({ category: "Electronics" });

res.json({
  total: totalProducts,        // Estimate 1250
  electronics: electronicsCount // Accurate 250
});

// === mongosh Iterate through the middle ===
db.products.find({ category: "Electronics" }).forEach(doc => {
  print(`SKU: ${doc.sku}, Title: ${doc.title}`);
});

// === Node.js Iterate through the middle ===
const cursor = collection.find({ category: "Electronics" });

// Methods 1:toArray()
const products = await cursor.toArray();

// Methods 2:for await...of
for await (const doc of collection.find({ category: "Electronics" })) {
  console.log(doc.title);
}

// Methods 3:Manual next()
const cursor2 = collection.find({ category: "Electronics" });
while (await cursor2.hasNext()) {
  const doc = await cursor2.next();
  console.log(doc);
}

// === Set the batch size ===
const cursor = collection.find({ category: "Electronics" })
  .batchSize(100);  // Per batch 100 items

// === Limit the Maximum Return ===
const cursor = collection.find({ category: "Electronics" })
  .limit(1000);

// === Limit Cursor Timeout ===
const cursor = collection.find({ category: "Electronics" })
  .maxTimeMS(5000);  // 5 Timeout in seconds

// === Complete mongoose Query Chain ===
const products = await Product.find({ category: "Electronics" })
  .where('price').gt(100).lt(1000)        // Price 100-1000
  .where('stock').gt(0)                   // In stock
  .select('sku title price')              // Projection
  .sort({ price: 1 })                     // Sort
  .skip(20)                               // Pagination
  .limit(10)                              // Restrictions
  .populate('categoryId', 'name slug')    // Joined Queries
  .lean();                                // Performance Optimization

// === Equivalent, concise notation ===
const products2 = await Product.find({
  category: "Electronics",
  price: { $gt: 100, $lt: 1000 },
  stock: { $gt: 0 }
})
  .select('sku title price')
  .sort({ price: 1 })
  .skip(20)
  .limit(10)
  .lean();

// === Scene:E-commerce Product Search API ===
app.get('/api/products/search', async (req, res) => {
  const { q, category, minPrice, maxPrice, sortBy = 'relevance' } = req.query;

  // 1. Build a Query
  const query = { isActive: true };
  if (q) query.$text = { $search: q };
  if (category) query.category = category;
  if (minPrice || maxPrice) {
    query.price = {};
    if (minPrice) query.price.$gte = NumberDecimal(minPrice);
    if (maxPrice) query.price.$lte = NumberDecimal(maxPrice);
  }

  // 2. Sort
  const sortObj = sortBy === 'price_asc' ? { price: 1 } :
                  sortBy === 'price_desc' ? { price: -1 } :
                  sortBy === 'newest' ? { createdAt: -1 } :
                  { score: { $meta: 'textScore' } };  // Full-Text Search with Relevance Ranking

  // 3. Search
  const products = await Product.find(query, sortObj.score ? { score: { $meta: 'textScore' } } : {})
    .sort(sortObj)
    .limit(40)
    .lean();

  // 4. Statistics
  const total = await Product.countDocuments(query);

  res.json({
    query: { q, category, minPrice, maxPrice },
    total,
    products
  });
});