CODEWITHSUNDEEP
arraysmongodb
zeugor
zeugor

Reputation: 936

Find documents whose array field contain some subsets in MongoDB

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.

Upvotes: 2

Views: 564

Answers (2)

Mario Trucco
Mario Trucco

Reputation: 2011

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

Upvotes: 2

stephan
stephan

Reputation: 705

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" } ]}
        ]
    }
);

Upvotes: 2

Related Questions