I have ~ 200,000 documents in a collection which look like this:
{
"_id": "tdhABqSZPEZ2fFcEzOVCb-q8d",
"user": "testuser",
"content": "Test Content"
}
And I have an array with ~50,000 entries:
let arr = ["tree", "apple", "test", "orange", ...otherEntries] // ~ 50,000 entries
I want to get all documents where any element of the array is in the content value, non case-sensitive, so that the example document above would be returned because in the array is test and in the content of the document is Test.
This would work using $where and then using a JavaScript expression but this is not very fast.
Is there a really fast way (< 1-2 seconds) of doing a query like this or do you have any idea on how to restructure the documents that I can perform a fast query like this?
If you list(arr) was small => you could use make one index and use $in and filter (but its not small)
if you wanted case sensitive => you could make the list to a collection and $lookup with indexes (but you want case insensitive)
In you case that you have big list, and you want case insesitive
join the list into a big string(lets name it MyListString its a variable in your driver), separated with spaces
for example ["hat" "tree"] to become "hat tree"
create a text index on content its very easy to do for example in Java i did mycoll.createIndex(Indexes.text("content")); see your driver documentation on how to create text indexes.
Do a find or an aggregation with match (MyListString is the above big string variable) (this does by default a case insensitive match)
{ "$match" { "$text" { "$search" MyListString} } }
Time was < 1 sec in my benchmark, test it, i think you will be fine.