Find documents whose array field contain some subsets in MongoDB

Question

The "users" collection has documents with an array field.

Example documents:

{
    "_id" :1001,
    "properties" : ["A", "B", "C", "D", "E", "F", "G", "H", "I"]
}
{
    "_id" : 1002,
    "properties" : ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10"]
}

How can I build a query to get the documents which follow the next condition? Get only the documents that have the properties:

[ "3" AND ("A" OR "1") AND ("B" OR "2") ]

or in other way:

    "3" AND "A" AND "B"
OR
    "3" AND "A" AND "2"
OR
    "3" AND "1" AND "B"
OR
    "3" AND "1" AND "2"

In the previous example, the query has to result only the document:

{
    "_id" : 1002,
    "properties" : ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10"]
}

The collection has 4 million documents. The document array "properties" field has average length of 15 elements. The query I am looking for must have a good performance in this quite a big collection.

Mario Trucco · Accepted Answer · 2016-01-24 08:50:49Z

2

Stephan's answer is ok. Other ways to achieve the result using $in and $all operators:

db.users.find(
    {
    $and:[
        {"properties":"3"},
        {"properties" : {$in: ["A", "1"]}},
        {"properties" : {$in: ["B", "2"]}}
    ]
    }
);

(translation of your first description of the subset)

And

db.users.find(
    {
       $or: [
        {"properties" : {$all: ["3", "A", "B"]}},
        {"properties" : {$all: ["3", "A", "2"]}},
        {"properties" : {$all: ["3", "1", "B"]}},
        {"properties" : {$all: ["3", "1", "2"]}}
    ]
    }
);

(translation of your second description of the subset)

I'm afraid I can't tell which one will ensure the best performance. I hope that you have and index on properties.

You may try the queries on a smaller collection with explain to see the execution plan

edited Jan 24, 2016 at 8:50

answered Jan 24, 2016 at 8:20

Mario Trucco

2,0213 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

stephan · Accepted Answer · 2016-01-24 00:20:43Z

2

try this:

db.users.find(
    {
        $or: [
            {$and: [{ "properties": "3" }, { "properties": "A" }, { "properties": "B" }]},
            {$and: [{ "properties": "3" }, { "properties": "A" }, { "properties": "2" }]},
            {$and: [{ "properties": "3" }, { "properties": "1" }, { "properties": "B" }]},
            {$and: [{ "properties": "3" }, { "properties": "1" }, { "properties": "2" }]}
        ]
    }
);

or

db.users.find(
    {
        $and: [
            {"properties": "3" },
            {$or: [ { "properties": "A" }, { "properties": "1" } ]},
            {$or: [ { "properties": "B" }, { "properties": "2" } ]}
        ]
    }
);

edited Jan 24, 2016 at 0:20

answered Jan 23, 2016 at 22:27

stephan

7151 gold badge7 silver badges11 bronze badges

Collectives™ on Stack Overflow

Find documents whose array field contain some subsets in MongoDB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related