[SERVER-378] provide a way to query for combined keys with $in Created: 21/Oct/09  Updated: 06/Dec/22  Resolved: 24/Jan/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Swen Thümmler Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query
Participants:

 Description   

It would be great it mongodb could support queries for combined keys with $in
In SQL, this would be something like SELECT * FROM t WHERE (c1, c2) IN ((?, ?), (?, ?), (?, ?))
I'm not sure about the possible syntax of this command and how to distinguish it from queries for sub-objects, but it would be very useful for me. At the moment I have to introduce surrogate fields which contain the values from the fields to be queried, but this is not ideal.



 Comments   
Comment by Charlie Swanson [ 24/Jan/17 ]

Hi grmblfrz,

Thank you for the feature request, but I believe this functionality is already possibly by re-writing the query to something like the following:

> db.t.find({$or: [{c1: "option 1", c2: 1}, {c1: "option 2", c2: 2}, {c1: "option 3", c2: 3}]})

Alternatively, now that we have resolved SERVER-6146 which added an $in expression to the aggregation framework, you could express it more succinctly in this way:

> db.t.aggregate([{$addFields: {containsTuple: {$in: [["$c1", "$c2"], [["option 1", 1], ["option 2", 2], ["option 3", 3]]}}}}, {$match: {containsTuple: true}}])

Please note that using the aggregation framework in this way may impact performance, as we likely will not find relevant indices for that query.

Given that there are two workarounds, and it's unclear what the syntax would look like, I am closing this ticket as "Won't Fix". Please feel free to re-open if you have a proposal for better syntax, and we will reconsider.

Generated at Thu Feb 08 02:53:53 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.