Hi,
Im working on a database where user can collect cards from different tcg games.
I have a collection of cards and collection of user_collection having an array of cards ids.
I want to allow user searching in a card by name in their collection but the aggregation im dowing is too slow (4seconds response)
I have 20k cards, and 5 entries in user_collection having cards arrays with a global 1k cards ids.
[
{
// Step 1: Use Atlas Search to find cards by name containing "q"
$search: {
index: "cards_index", // Use the name of the search index you created
wildcard: {
query: "*a*",
path: "name.en",
}
}
},
{
$project: {
meta: "$$SEARCH_META",
card: "$$ROOT",
paginationToken: {
$meta: "searchSequenceToken"
}
}
},
{
// Step 2: Join with UserCollection, filtering by user ID
$lookup: {
from: "user_collections",
let: { objid: "$_id" },
pipeline: [
{
$match: {
$expr: {
$and: [
{ $in: ["$$objid", "$cards._id"] }, // Match card IDs
{ $eq: ["$_id.user", ObjectId("66ab683743c06d6b54eb7fdd")] } // Match specific user ID
]
}
}
}
],
as: "userCards"
}
},
{
// Step 3: Filter out cards that don't have matching entries in UserCollection
$match: {
userCards: { $ne: [] }
}
},
{
$limit: 100
}
]
Im new in mongo atlas, but globally what i want to do is something like:
SELECT FROM cards WHERE Name LIKE "%a%" AND id IN (SELECT id FROM user_collection WHERE user_id = "xxx")
Any help would be great. Ty.