1 / 1
Aug 2024

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.