[SERVER-65808] $search as subpipeline of $lookup with localField+foreignField is not supported Created: 20/Apr/22  Updated: 29/Oct/23  Resolved: 29/Apr/22

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 6.0.0-rc0
Fix Version/s: 6.0.0-rc4, 6.1.0-rc0

Type: Improvement Priority: Major - P3
Reporter: Katya Kamenieva Assignee: Ruslan Abdulkhalikov (Inactive)
Resolution: Fixed Votes: 0
Labels: search
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Documented
is documented by DOCS-15298 [Server] Investigate changes in SERVE... Closed
Related
related to SERVER-60800 Allow $search in $lookup/$unionWith Closed
Backwards Compatibility: Minor Change
Backport Requested:
v6.0
Sprint: QO 2022-05-02
Participants:

 Description   

A pipeline like this works as expected:

[{
  "$lookup": {
    "from": "suppliers",
    "let": { "s": "address.state" },
    "as": "details",
    "pipeline": [
      {
        "$search": {
          "text": {
            "query": "green",
            "path": "description"
          }
        }
      },
      { "$match": { "$expr": { "$eq": ["$$s", "$address.state"] } } }
    ]
  }
}
] 

But equivalent expressed with localField/foreignField produces an error.

 
db.test.aggregate([{ 
  "$lookup": { 
    "from": "suppliers", 
    "localField": "address.state", 
    "foreignField": "address.state", 
    "as": "details", 
    "pipeline": [ 
      { 
        "$search": { 
          "text": { 
            "query": "green", 
            "path": "description" 
          } 
        } 
      } 
    ] 
  } 
}]) 

"$_internalSearchMongotRemote is only valid as the first stage in a pipeline"



 Comments   
Comment by Githook User [ 29/Apr/22 ]

Author:

{'name': 'ruslan.abdulkhalikov', 'email': 'ruslan.abdulkhalikov@mongodb.com', 'username': 'rusabd1'}

Message: SERVER-65808 support $search as subpipeline of $lookup with localField+foreignField

(cherry picked from commit 736d9efe5ef102c2d4e5de5d268787030cd4c346)
Branch: v6.0
https://github.com/mongodb/mongo/commit/735345972ec5a3c5c8ce70dbb07423807a1de506

Comment by Githook User [ 29/Apr/22 ]

Author:

{'name': 'ruslan.abdulkhalikov', 'email': 'ruslan.abdulkhalikov@mongodb.com', 'username': 'rusabd1'}

Message: SERVER-65808 support $search as subpipeline of $lookup with localField+foreignField

(cherry picked from commit d24a16c3ba4902526b9ce60ce245a2dc473b3de3)
Branch: v6.0
https://github.com/10gen/mongo-enterprise-modules/commit/f25871d3a275519d69056355aa573b46a43536b7

Comment by Githook User [ 28/Apr/22 ]

Author:

{'name': 'ruslan.abdulkhalikov', 'email': 'ruslan.abdulkhalikov@mongodb.com', 'username': 'rusabd1'}

Message: SERVER-65808 support $search as subpipeline of $lookup with localField+foreignField
Branch: master
https://github.com/mongodb/mongo/commit/73f366b49fab43b55ba61b31f37d48733e158213

Comment by Githook User [ 28/Apr/22 ]

Author:

{'name': 'ruslan.abdulkhalikov', 'email': 'ruslan.abdulkhalikov@mongodb.com', 'username': 'rusabd1'}

Message: SERVER-65808 support $search as subpipeline of $lookup with localField+foreignField
Branch: master
https://github.com/10gen/mongo-enterprise-modules/commit/d24a16c3ba4902526b9ce60ce245a2dc473b3de3

Comment by David Storch [ 21/Apr/22 ]

jonathan.streets@mongodb.com kateryna.kamenieva@mongodb.com ruslan.abdulkhalikov@mongodb.com I'm wondering why this was marked "6.0 Required", could you elaborate? This behavior was purposeful when we first implemented SERVER-60800, so I'm curious for more context here.

Comment by David Storch [ 20/Apr/22 ]

The error message isn't very good, but this was definitely intentionally excluded from our original efforts to allow $search inside a $lookup sub-pipeline. For this reason, I'm going to convert this ticket from type "Bug" to type "Improvement".

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