Hello,Dose mongo has some way can like this query?
HI @Zheng_Ficoto,
Please provide more information on your topic:
- example document
- example query
- expected results
- version of MongoDB server
Thanks,
Stennie
WHERE (id1, id2) IN ((1, 2), (3, 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
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.
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
I hope can use tuple to query,is mongo support using tuple to query?
- 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”)))