About find $in query

Working with Data
1

Hello,Dose mongo has some way can like this query?

2

HI @Zheng_Ficoto,

Please provide more information on your topic:

  • example document
  • example query
  • expected results
  • version of MongoDB server

Thanks,
Stennie

3

WHERE (id1, id2) IN ((1, 2), (3, 4))

4

Hi,

Please see the SQL to MongoDB Mapping Chart for a general guide to equivalent statements. As mentioned earlier, more information would be helpful to understand what you are trying to achieve.

I believe you are looking for results matching id1 (value of 1 or 2) or id2 (value of 3 or 4), so the equivalent query in the mongo shell would be:

db.mydata.find(
    { $or: [
        { id1:{ $in: [1,2] } },
        { id2:{ $in: [3,4] } }
    ]}
)

Regards,
Stennie

5

Hi, I do not mean that,I want to query such as this

db.mydata.find(
    { $or: [
        { id1:1,id2:2},
        { id1:3,id2:4}
    ]}
)

but I need some other way to query,because $or sometime make query cannt using the right index,then query is too slowly.So,Is having other way to optimizeo some query like this?Except using hint(),thanks.

6

Hi @Zheng_Ficoto,

It sounds like your question may be about query performance rather than constructing a query.

Please provide more information to help understand your issue:

  • example document
  • example query with explain(true) output
  • expected results
  • version of MongoDB server

Thanks,
Stennie

7

I hope can use tuple to query,is mongo support using tuple to query?

8
  • example query
db.getCollection('lesson_consume_log').find({  
    "$or": [{  
      "order_num": "5f3e17dc0b586f0001a2e519",  
      "lesson_id": ObjectId("5e9013f31f6cfc00018e30ee")  
    }, {  
      "order_num": "5f3e17dc0b586f0001a2e519",  
      "lesson_id": ObjectId("5e9014351f6cfc00018e3103")  
    }, {  
      "order_num": "5f3e17dc0b586f0001a2e519",  
      "lesson_id": ObjectId("5e9014599976c5000161f330")  
    }],  
    "op_time": {  
      "$gte": ISODate("2020-08-20T00:00:00.000+0800"),  
      "$lte": ISODate("2020-08-20T23:59:59.999+0800")  
    }  
  }).explain(true)
  • query plan
{  
  "stage": "FETCH",  
  "filter": {  
    "$and": [{  
        "op_time": {  
          "$lte": ISODate("2020-08-20T15:59:59.999Z")  
        }  
      },  
      {  
        "op_time": {  
          "$gte": ISODate("2020-08-19T16:00:00.000Z")  
        }  
      }  
    ]  
  },  
  "inputStage": {  
    "stage": "OR",  
    "inputStages": [{  
        "stage": "FETCH",  
        "filter": {  
          "order_num": {  
            "$eq": "5f3e17dc0b586f0001a2e519"  
          }  
        },  
        "inputStage": {  
          "stage": "IXSCAN",  
          "keyPattern": {  
            "lesson_id": 1.0  
          },  
          "indexName": "lesson_id_1",  
          "isMultiKey": false,  
          "multiKeyPaths": {  
            "lesson_id": []  
          },  
          "isUnique": false,  
          "isSparse": false,  
          "isPartial": false,  
          "indexVersion": 2,  
          "direction": "forward",  
          "indexBounds": {  
            "lesson_id": [  
              "[ObjectId('5e9013f31f6cfc00018e30ee'), ObjectId('5e9013f31f6cfc00018e30ee')]"  
            ]  
          }  
        }  
      },  
      {  
        "stage": "IXSCAN",  
        "keyPattern": {  
          "order_num": 1,  
          "lesson_id": 1  
        },  
        "indexName": "order_num_1_lesson_id_1",  
        "isMultiKey": false,  
        "multiKeyPaths": {  
          "order_num": [],  
          "lesson_id": []  
        },  
        "isUnique": false,  
        "isSparse": false,  
        "isPartial": false,  
        "indexVersion": 2,  
        "direction": "forward",  
        "indexBounds": {  
          "order_num": [  
            "[\"5f3e17dc0b586f0001a2e519\", \"5f3e17dc0b586f0001a2e519\"]"  
          ],  
          "lesson_id": [  
            "[ObjectId('5e9014351f6cfc00018e3103'), ObjectId('5e9014351f6cfc00018e3103')]"  
          ]  
        }  
      },  
      {  
        "stage": "IXSCAN",  
        "keyPattern": {  
          "order_num": 1,  
          "lesson_id": 1  
        },  
        "indexName": "order_num_1_lesson_id_1",  
        "isMultiKey": false,  
        "multiKeyPaths": {  
          "order_num": [],  
          "lesson_id": []  
        },  
        "isUnique": false,  
        "isSparse": false,  
        "isPartial": false,  
        "indexVersion": 2,  
        "direction": "forward",  
        "indexBounds": {  
          "order_num": [  
            "[\"5f3e17dc0b586f0001a2e519\", \"5f3e17dc0b586f0001a2e519\"]"  
          ],  
          "lesson_id": [  
            "[ObjectId('5e9014599976c5000161f330'), ObjectId('5e9014599976c5000161f330')]"  
          ]  
        }  
      }  
    ]  
  }  
}

I hope this query only use “order_num_1_lesson_id_1”,but it need using hint(),and I hope it can use tuple query like this

WHERE (order_num, lesson_id) IN (
(“5f3e17dc0b586f0001a2e519”, ObjectId(‘5e9014599976c5000161f330’)),
(“5f3e17dc0b586f0001a2e519”, ObjectId(“5e9014351f6cfc00018e3103”)),
(“5f3e17dc0b586f0001a2e519”, ObjectId(“5e9013f31f6cfc00018e30ee”)))