{"expand":"renderedFields,names,schema,operations,editmeta,changelog,versionedRepresentations","id":"411508","self":"https://jira.mongodb.org/rest/api/2/issue/411508","key":"SERVER-30474","fields":{"comment":{"comments":[{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/1909120","id":"1909120","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=kevin.pulo%40mongodb.com","name":"kevin.pulo@mongodb.com","key":"kevin.pulo@10gen.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=kevin.pulo%4010gen.com&avatarId=20011","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=kevin.pulo%4010gen.com&avatarId=20011","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=kevin.pulo%4010gen.com&avatarId=20011","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=kevin.pulo%4010gen.com&avatarId=20011"},"displayName":"Kevin Pulo","active":true,"timeZone":"Australia/Sydney"},"body":"The approach of promoting the {{$match}} out of the {{$facet}} is fine for exact matches, but not for more complex situations, eg.\r\n\r\n{code}\r\ndb.collection.aggregate([\r\n  {$facet: {\r\n    x1: [\r\n      {$match: {x: {$gte: somevalue}}},\r\n      {$limit: 1}\r\n    ],\r\n    x2: [\r\n      {$match: {x: {$lte: someothervalue}}},\r\n      {$limit: 1}\r\n    ]\r\n  }}\r\n])\r\n{code}\r\n\r\nWhen run as independent aggregations, these each scan just 1 document.  But when run as above inside {{$facet}}, the whole collection gets scanned.\r\n\r\nRewriting this as suggested above would give:\r\n\r\n{code}\r\ndb.collection.aggregate([\r\n  {$match: { $or: [ {x: {$gte: somevalue}}, {x: {$lte: someothervalue}}]}},\r\n  {$facet: {\r\n    x1: [\r\n      {$match: {x: {$gte: somevalue}}},\r\n      {$limit: 1}\r\n    ],\r\n    x2: [\r\n      {$match: {x: {$lte: someothervalue}}},\r\n      {$limit: 1}\r\n    ]\r\n  }}\r\n])\r\n{code}\r\n\r\nIn this case, the leading {{$match}} likely gives no benefit, since up to the entire collection will still be passed into {{$facet}}.\r\n\r\nI think what's really needed is, when {{$facet}} is the first stage, each of its sub-pipelines should be run as if it was an actual aggregation (ie. with full agg query planning), like the way that {{$lookup}} with a pipeline does.  This would allow both this aggregation and the one originally posted on this ticket, to run with full efficiency (without any rewriting).","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=kevin.pulo%40mongodb.com","name":"kevin.pulo@mongodb.com","key":"kevin.pulo@10gen.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=kevin.pulo%4010gen.com&avatarId=20011","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=kevin.pulo%4010gen.com&avatarId=20011","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=kevin.pulo%4010gen.com&avatarId=20011","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=kevin.pulo%4010gen.com&avatarId=20011"},"displayName":"Kevin Pulo","active":true,"timeZone":"Australia/Sydney"},"created":"2018-06-03T06:51:16.000+0000","updated":"2018-06-03T06:51:16.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/2098151","id":"2098151","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=aniket%40vrhythmstech.com","name":"aniket@vrhythmstech.com","key":"aniket@vrhythmstech.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=10122","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=10122","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=10122","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=10122"},"displayName":"aniket patil","active":true,"timeZone":"UTC"},"body":"It looks like this is must needed functionality to take advantage of $facet aggregation operator. If $facet aggregation stage does not use index created for the sub pipelines then the purpose of use $facet is killed. Please take this issue into account.","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=aniket%40vrhythmstech.com","name":"aniket@vrhythmstech.com","key":"aniket@vrhythmstech.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=10122","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=10122","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=10122","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=10122"},"displayName":"aniket patil","active":true,"timeZone":"UTC"},"created":"2018-12-21T09:28:11.000+0000","updated":"2018-12-21T09:28:11.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/2104019","id":"2104019","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=aniket%40vrhythmstech.com","name":"aniket@vrhythmstech.com","key":"aniket@vrhythmstech.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=10122","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=10122","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=10122","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=10122"},"displayName":"aniket patil","active":true,"timeZone":"UTC"},"body":"Any update on this. How to pushed up this issue from backlog bucket.","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=aniket%40vrhythmstech.com","name":"aniket@vrhythmstech.com","key":"aniket@vrhythmstech.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=10122","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=10122","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=10122","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=10122"},"displayName":"aniket patil","active":true,"timeZone":"UTC"},"created":"2019-01-03T05:28:07.000+0000","updated":"2019-01-03T05:28:07.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/2115203","id":"2115203","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=inking007","name":"inking007","key":"inking007","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=17814","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=17814","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=17814","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=17814"},"displayName":"Intae Kim","active":true,"timeZone":"Asia/Seoul"},"body":"On Mongodb CE 3.6.9, I  tried using 'facet'  to make query like UNION of RDBMS's SQL. but Facet was very slow even though I wrote query with $match of indexed fields. Is there any other query like  UNION of SQL?","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=inking007","name":"inking007","key":"inking007","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=17814","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=17814","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=17814","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=17814"},"displayName":"Intae Kim","active":true,"timeZone":"Asia/Seoul"},"created":"2019-01-15T06:24:42.000+0000","updated":"2019-01-15T06:24:42.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/2116521","id":"2116521","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=asya.kamsky%40mongodb.com","name":"asya.kamsky@mongodb.com","key":"asya","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=asya&avatarId=11415","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=asya&avatarId=11415","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=asya&avatarId=11415","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=asya&avatarId=11415"},"displayName":"Asya Kamsky","active":true,"timeZone":"America/New_York"},"body":"[~inking007] please watch (and upvote) SERVER-23201 for Union equivalent expression.\r\n","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=asya.kamsky%40mongodb.com","name":"asya.kamsky@mongodb.com","key":"asya","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=asya&avatarId=11415","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=asya&avatarId=11415","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=asya&avatarId=11415","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=asya&avatarId=11415"},"displayName":"Asya Kamsky","active":true,"timeZone":"America/New_York"},"created":"2019-01-16T01:02:40.000+0000","updated":"2019-01-16T01:02:40.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/2642239","id":"2642239","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=dgreat.rishi%40gmail.com","name":"dgreat.rishi@gmail.com","key":"dgreat.rishi@gmail.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=10122","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=10122","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=10122","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=10122"},"displayName":"Rishi Mishra","active":true,"timeZone":"UTC"},"body":"Is this feature getting implemented anytime soon ? \r\n\r\nIf I add $match with $or clause having all conditions which are the in facet will it be any beneficial ? \r\n\r\ndoes adding $match at first , will reduce number of document to be scanned by facet or it will go with all collection document irrespective of $match ?","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=dgreat.rishi%40gmail.com","name":"dgreat.rishi@gmail.com","key":"dgreat.rishi@gmail.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=10122","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=10122","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=10122","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=10122"},"displayName":"Rishi Mishra","active":true,"timeZone":"UTC"},"created":"2019-12-18T10:29:34.000+0000","updated":"2019-12-18T10:29:34.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/2699426","id":"2699426","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=asya.kamsky%40mongodb.com","name":"asya.kamsky@mongodb.com","key":"asya","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=asya&avatarId=11415","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=asya&avatarId=11415","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=asya&avatarId=11415","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=asya&avatarId=11415"},"displayName":"Asya Kamsky","active":true,"timeZone":"America/New_York"},"body":"> If I add $match with $or clause having all conditions which are the in facet will it be any beneficial ? \r\n\r\nIts benefit will depend on how selective it is.  If it's selective relative to total size of the collection then it should benefit the overall performance significantly (assuming there is an index that can be used).\r\n","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=asya.kamsky%40mongodb.com","name":"asya.kamsky@mongodb.com","key":"asya","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=asya&avatarId=11415","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=asya&avatarId=11415","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=asya&avatarId=11415","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=asya&avatarId=11415"},"displayName":"Asya Kamsky","active":true,"timeZone":"America/New_York"},"created":"2020-01-02T21:33:25.000+0000","updated":"2020-01-02T21:33:25.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/3193658","id":"3193658","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=daniele.tassone2008%40gmail.com","name":"daniele.tassone2008@gmail.com","key":"daniele.tassone2008@gmail.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=17825","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=17825","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=17825","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=17825"},"displayName":"DANIELE Tassone","active":true,"timeZone":"UTC"},"body":"While I was using $facet to try optimise a complex query scenario, I got this blocking issue.\r\nWould be great to improve this feature.","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=daniele.tassone2008%40gmail.com","name":"daniele.tassone2008@gmail.com","key":"daniele.tassone2008@gmail.com","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?avatarId=17825","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&avatarId=17825","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&avatarId=17825","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&avatarId=17825"},"displayName":"DANIELE Tassone","active":true,"timeZone":"UTC"},"created":"2020-06-06T10:20:31.000+0000","updated":"2020-06-06T10:20:31.000+0000"},{"self":"https://jira.mongodb.org/rest/api/2/issue/411508/comment/3370459","id":"3370459","author":{"self":"https://jira.mongodb.org/rest/api/2/user?username=asya.kamsky%40mongodb.com","name":"asya.kamsky@mongodb.com","key":"asya","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=asya&avatarId=11415","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=asya&avatarId=11415","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=asya&avatarId=11415","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=asya&avatarId=11415"},"displayName":"Asya Kamsky","active":true,"timeZone":"America/New_York"},"body":"Ignore my previous comment - was testing a different issue ","updateAuthor":{"self":"https://jira.mongodb.org/rest/api/2/user?username=asya.kamsky%40mongodb.com","name":"asya.kamsky@mongodb.com","key":"asya","avatarUrls":{"48x48":"https://jira.mongodb.org/secure/useravatar?ownerId=asya&avatarId=11415","24x24":"https://jira.mongodb.org/secure/useravatar?size=small&ownerId=asya&avatarId=11415","16x16":"https://jira.mongodb.org/secure/useravatar?size=xsmall&ownerId=asya&avatarId=11415","32x32":"https://jira.mongodb.org/secure/useravatar?size=medium&ownerId=asya&avatarId=11415"},"displayName":"Asya Kamsky","active":true,"timeZone":"America/New_York"},"created":"2020-09-01T16:13:09.000+0000","updated":"2020-09-01T16:13:09.000+0000"}],"maxResults":1000,"total":9,"startAt":0}},"renderedFields":null}