[SERVER-10789] User-Parameterized Views Created: 16/Sep/13 Updated: 21/Jun/23 Resolved: 21/Jun/23 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Usability |
| Affects Version/s: | None |
| Fix Version/s: | 7.0.0-rc0 |
| Type: | New Feature | Priority: | Major - P3 |
| Reporter: | Andy Schwerin | Assignee: | Backlog - Query Optimization |
| Resolution: | Done | Votes: | 9 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||
| Participants: | |||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||
| Description |
|
Supposing |
| Comments |
| Comment by Katya Kamenieva [ 21/Jun/23 ] | ||||
|
New system variable $$USER_ROLES returns roles of the current database user. A pipeline of the view definition can refer to this variable when expressing a condition for what data will be shown by the view.
Re-opening ticket SERVER-45433 to track support for user-defined variables in the view definition. | ||||
| Comment by Brady Byrd [ 18/Jan/23 ] | ||||
|
I'd really love to see this feature. I'm finding that this capability is absolutely essential to make lookups and translated sql queries performant. db.createView("myView", "myColl", [ {$match : { mykey: {$parameter: {type: "string", default: ""}}}, {$lookup : { from...}} ]
| ||||
| Comment by Suvra Banerjee [ 04/May/22 ] | ||||
|
Could you please expedite this passing parameter to Mongo view? Without this views are too slow and it is impacting our production system as the data grows in underlying collection, views are becoming too slow. We can filter data lot earlier in the pipeline if we get to pass value to matching parameter.
Regards, Suvra | ||||
| Comment by Andrzej Taramina [ 01/Mar/21 ] | ||||
|
We're keen on having parametrized views as well! | ||||
| Comment by Katya Kamenieva [ 24/Jan/20 ] | ||||
|
Adding this request to backlog for prioritization. | ||||
| Comment by Asya Kamsky [ 04/Jan/19 ] | ||||
|
sgaylord@facilitron.com if the view you created is not sharded you can run the aggregation in it with $out to create a collection with the results and then do a selective $lookup from that collection (using an indexed field to join). | ||||
| Comment by Asya Kamsky [ 04/Jan/19 ] | ||||
|
sgaylord@facilitron.com I think what you are describing is being tracked by | ||||
| Comment by Steve Gaylord [ 03/Jan/19 ] | ||||
|
Yes, we do have a group in the view. Let me give you more details of our use case. We have three collections, collection one has the parent document and collection two and three have child documents. I need to see the count of the documents in collection two and if that is zero I need the documents from collection three, so I created a view that looks at collection two and groups them for each parent document and then looks at collection three for those parents that do not have any child documents in collection two and then do a $project to get the appropriate array of documents from collection two or collection three. Then when I call an aggregate on the view or use it in a $lookup, I pass in the _id of the parent document I am looking for. But it looks like the view takes the $match I pass in the aggregate or $lookup and places it after the view pipeline so I get a collscan. Does that help define the use case? | ||||
| Comment by Asya Kamsky [ 03/Jan/19 ] | ||||
|
sgaylord@facilitron.com aggregation pipeline has the ability to reorder stages to optimize the query plan it's able to use. Does your view involve $group (or similar) stage where you only want a subset of the results? | ||||
| Comment by Steve Gaylord [ 02/Jan/19 ] | ||||
|
We would like something like this as well. We would like to create the view but pass in the matching characteristics at run time, but currently that match is placed at the end of the pipeline. We have two collections and we want to pull data from one or the other for a specific entry passed in at runtime, so the view needs to be on the entire collection and then pass the match the specific entity at runtime. This causes the view to do a COLLSCAN when called. | ||||
| Comment by Bruno R. [ 30/Aug/18 ] | ||||
|
Hi Andy, You mean any given input parameter? If yes, that's exactly what I'm looking for as well. Example: A very simple aggregate to group information given a number of last days:
In this case, I would like to create a view that can receive as input parameter the number of days, so that I don't need to hard-code "30" in the $match. |