[SERVER-26959] Make text queries work on views whenever possible Created: 08/Nov/16  Updated: 22/Mar/23

Status: Backlog
Project: Core Server
Component/s: Diagnostics, Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Kyle Suarez Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 1
Labels: read-only-views
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Related
is related to SERVER-42314 Test that $search cannot be used on a... Closed
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Participants:
Case:

 Description   

Currently, text queries are not allowed on views. However, we should try to let them succeed whenever possible, and the code that looks for text indexes should be views-aware. Because text matches must be the first stage in a pipeline, this can only be allowed if it's safe to let the match float to the front of the pipeline.

When doing this, we should be careful to ensure that this transformation does not leak sensitive information in the view pipeline.

Original Description

We should consider whether or not to add a specific error message that explicitly mentions that $text queries are not supported on views. If a user attempts such an operation, they'll get an IndexNotFound message:

> db.view.find( { "$text": { "$search": "foo" } } )
Error: error: {
        "ok" : 0,
        "errmsg" : "text index required for $text query (no such collection 'test.view')",
        "code" : 27,
        "codeName" : "IndexNotFound"
}

This will always happen, even if the underlying collection on the view does have a text index. (A user might also be confused at the message stating "no such collection 'test.view'", especially if they're given an opaque handle to something that may be either a view or a collection.) The user might then try

> db.view.ensureIndex( { x: "text" } )
{
        "ok" : 0,
        "errmsg" : "Cannot create indexes on a view",
        "code" : 166,
        "codeName" : "CommandNotSupportedOnView"
}

Perhaps it would be easier on the user to simply detect this up-front?



 Comments   
Comment by Kyle Suarez [ 28/Nov/17 ]

Hello ashraf.seventech@gmail.com,

No, you cannot perform a $text query on a view. Again, the fix version of this ticket is marked as "Backlog", meaning that we have not yet scheduled to work on it. Please vote for this issue, so that we know how to prioritize this improvement when planning for future releases.

Regards,
Kyle

Comment by Ashraf Kamal [ 23/Nov/17 ]

Hi Kyle

Oh! you mean the $text query was not working on view is fixed? wow the it would be good news for us.

Please let us know when it is released.

Comment by Kyle Suarez [ 22/Nov/17 ]

Hey ashraf.seventech@gmail.com,

Sorry, that wasn't a great example. In your case, the text search varies with user input, so you cannot perform your text search on a view. Instead, you will have to perform the text search against the base collection.

For example, if you had a view that filters food based on type:

db.createView("vegetablesView", "foodCollection", [{$project: {type: 1, name: 1}}, {$match: {type: "vegetable"}}])

You could issue a query on "foodCollection" that both applies a text search and performs the view transformation:

db.foodCollection.aggregate([{$match: {$text: {$search: "your search here"}}}, {$project: {type: 1, name: 1}}, {$match: {type: "vegetable"}}]);

In general, though, this won't work for every view, depending on the what the view transformation pipeline is. Though we're aware of this limitation, this ticket has a fix version of "Backlog", meaning we have not yet scheduled it for an upcoming release. Please vote for this issue and follow the ticket for further updates.

I hope this helps!
Kyle

Comment by Ashraf Kamal [ 21/Nov/17 ]

Hi Kyle.
Can you tell me what do mean by "NJ" and state
yes I want to use for list page as well as for search like if user search then query should excute search query else list all query. so which one should I go for

Thanks

Comment by Kyle Suarez [ 15/Nov/17 ]

Hi ashraf.seventech@gmail.com,

In general you cannot issue an aggregation with $text matching on a view because the $text must be the first stage in the pipeline. When you query a view the view definition's pipeline always comes first.

I'd say there's three options for you, depending on what you're trying to accomplish. First, if the text search is something you always want to perform on the view, you can accomplish that by changing the view definition to include a $match with a $text search as the very first stage of the pipeline.

Second, if you don't always want to perform the text query but the text search string is fixed, you could also make a second view (say, textSearchView) that is identical to the first view except that it includes a $match with a $text search. For example, if you created a view like

db.createView("newJerseyUsers", "users", [{$match: {state: "NJ"}}, {$project: {ssn: 0}}]);

and you needed to make queries with a fixed text search, just make a second view:

db.createView("newJerseyUsers", "users", [{$match: {state: "NJ", $text: {$search: "foo"}}}, {$project: {ssn: 0}}]);

Lastly, if the text being searched for is variable or you wanted to perform ad-hoc queries, then you'd have to attempt a workaround by performing an aggregation on the underlying collection like I described above.

Kyle

Comment by Ashraf Kamal [ 15/Nov/17 ]

Hi Kyle Suarez,

Can you please tell me how to use aggregation pipeline for $text on view. I actually a beginner. It would be helpul for me if you give me one example to use aggregation pipeline.

Thanks

Comment by Kyle Suarez [ 09/Nov/17 ]

Hello ashraf.seventech@gmail.com,

Like I mentioned in the description of the ticket it is not possible to use a $text query on a view in the same way that you would use a $text query on a collection.

Recall that a view is just an aggregation pipeline that is applied to a collection (or another view). It might be possible to work around this limitation by issuing an aggregation pipeline with a $text query against the underlying collection. Note that a $text query has to be the first stage in the pipeline.

The Query Team is planning on improvements to $text search on views in the future. As you can see, the "fixVersion" here is "Backlog", meaning that we have not yet scheduled this work for a definitive release target. Please vote for this issue and continue watching this ticket for updates.

Regards,
Kyle

Comment by Ashraf Kamal [ 09/Nov/17 ]

HI,
I have same problem. $text query not working on view. cannot get search result from view. gives me an error:
Error: text index required for $text query (no such collection '5903123e384970199123b63175c00_tdev.<viewName>')

Please give me solution how can I use $text query on view. How can I get search result using $text query from view

Generated at Thu Feb 08 04:13:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.