如何在DocumentDB中高效实现排除用户已购商品的查询？

Great question! Your current approach of stacking != conditions will quickly become inefficient as the number of purchased items grows (like over 500) — it leads to long query strings, poor index utilization, and higher RU (Request Unit) consumption. Let’s break down the optimal solutions.

Why Your Current Approach Falls Short #

When you use a long list of c.id != X AND c.id != Y ..., Cosmos DB can’t leverage its indexing effectively. It ends up scanning more documents than necessary to filter out purchased items, which slows down the query and uses more resources.

Optimal Solution: NOT EXISTS with ARRAY_CONTAINS #

This approach mirrors the NOT EXISTS logic you’d use in MS SQL, and it’s far more scalable for large purchased item lists. Here’s how it works:

SELECT TOP 10 c.*
FROM c
WHERE NOT EXISTS (
    SELECT 1
    FROM u
    WHERE u.id = "UserA" 
      AND ARRAY_CONTAINS(u.PurchasedProductId, c.id)
)
ORDER BY c.LastUpdateTime DESC

How This Works: #

1. Target the User Document: The subquery quickly locates the UserA document using the primary key index on u.id (this is an O(1) lookup).
2. Check for Purchased Items: ARRAY_CONTAINS(u.PurchasedProductId, c.id) checks if the current item’s ID exists in the user’s purchased list. Cosmos DB automatically indexes array elements by default, so this check is efficient even for large arrays.
3. Filter and Sort: The outer query filters out items that are in the purchased list, then sorts the remaining items by LastUpdateTime (use a range index on this field for fast sorting) and returns the top 10.

Index Configuration Tips #

To maximize performance:

• User Collection: Ensure PurchasedProductId has indexing enabled (it’s on by default, but double-check if you’ve modified index policies).
• Item Collection: Add a range index on LastUpdateTime (default for string/numeric fields) to make the ORDER BY and TOP 10 operations efficient, avoiding full-collection scans and in-memory sorting.

For Extremely Large Purchased Lists (10k+ Items) #

If your users have thousands of purchased items, consider splitting purchase records into a dedicated UserPurchases collection where each document looks like:

{ "UserId": "UserA", "ProductId": "ProductId1", "PurchaseDate": "2024-01-01" }

Then use this optimized query:

SELECT TOP 10 c.*
FROM c
WHERE NOT EXISTS (
    SELECT 1
    FROM p
    WHERE p.UserId = "UserA" 
      AND p.ProductId = c.id
)
ORDER BY c.LastUpdateTime DESC

Add a composite index on (UserId, ProductId) to make the subquery lightning-fast — this is ideal for scaling to massive purchase histories.

Key Takeaways #

• Avoid long chains of != or NOT IN with large lists; they don’t scale well.
• Use NOT EXISTS with ARRAY_CONTAINS (or a dedicated purchase collection) for efficient, scalable filtering.
• Proper index configuration is critical to keep RU costs low and query speeds high.

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