[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:
Duplicate
is duplicated by SERVER-45433 Parameterized views Open
Related
is related to SERVER-10787 Read-only views Closed
is related to SERVER-20968 Non-materialized views Closed
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Participants:
Case:

 Description   

Supposing SERVER-10787 and maybe SERVER-10788, it would be nice to define a view whose contents are dependent on properties of the authenticated user searching it.



 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 SERVER-34741 and it's not necessarily related to this ticket (though views that took parameters could be a workaround for this use case).

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:

db.collection.aggregate([
    {$match: {created: {$gte: new Date((new Date().getTime() - (30 * 86400000)))}}},
    {$group: {_id: "$country", totalDocs: {$sum: 1}}
])

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.

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