Query a mongodb record comparing values in an array to the array passed in the query

Question

I have a document in the format

{
"history": [
{
  "plugin": [
    1,
    2,
    3,
    4
  ]
},
{
  "plugin": [
    2,
    3,
    4,
    6
  ]
},
{
  "plugin": [
    1,
    4
  ]
}
]
}

and i have an array x =[2,7],

I want query in such a way that if there is atleast one element in x which match with any of the element in the the plugin array, it should be return

thus for this case the expected result is

{
"history": [
{
  "plugin": [
    1,
    2,
    3,
    4
  ]
},
{
  "plugin": [
    2,
    3,
    4,
    6
  ]
}    
]
}

chridam · Accepted Answer · 2016-10-14 07:42:24Z

1

Run the following pipeline which uses the $filter operator to return a subset of the history array that passes a given specified condition. This condition makes use of the $setIntersection set operator to check the embedded plugin array if it intersects with the given input comparison array.

So for instance, the expression

{ $setIntersection: [ [ 1, 4 ],  [ 2, 7 ] ] }

will return an empty set (array []) since the two arrays do not intersect. Use this as basis to compare the result with the $ne comparison operator. This will return true when the compared two values are not equivalent and false when they are equivalent. Use this result to feed the "cond" expression so that it will filter the appropriate elements.

The following pipeline demonstrates this:

var arr = [2,7];
db.collection.aggregate([
    { "$match": { "_id": ObjectId("57ffe28591f567293497d924") } },  // <-- filter here      
    {
        "$project": {
            "history": {
                "$filter": {
                    "input": "$history",
                    "as": "item",
                    "cond": {
                        "$ne": [
                            { "$setIntersection": ["$$item.plugin", arr] },
                            []
                        ]
                    }
                }
            }
        }
    }
])

Sample Output

{
    "_id" : ObjectId("57ffe28591f567293497d924"),
    "history" : [ 
        {
            "plugin" : [ 
                1, 
                2, 
                3, 
                4
            ]
        }, 
        {
            "plugin" : [ 
                2, 
                3, 
                4, 
                6
            ]
        }
    ]
}

As an alternative solution (if your MongoDB version does not support the $filter operator), consider using a combination of the $setDifference and $map operators to return the filtered array:

var arr = [2,7];
db.collection.aggregate([
    { "$match": { "_id": ObjectId("57ffe28591f567293497d924") } },  // <-- filter here      
    {
        "$project": {
            "history": {
                 "$setDifference": [
                    {
                        "$map": {
                            "input": "$history",
                            "as": "item",
                            "in": {
                                "$cond": [
                                    {
                                        "$ne": [
                                            { "$setIntersection": ["$$item.plugin", arr] },
                                            []
                                        ]
                                    },
                                    "$$item",
                                    false
                                ]
                            }
                        }
                    },
                    [false]
                 ]
            }
        }
    }
])

edited Oct 14, 2016 at 7:42

answered Oct 13, 2016 at 19:53

chridam

104k26 gold badges246 silver badges243 bronze badges

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

6 Comments

Jibin Mathew Over a year ago

How do I add another condition to match the '_id'

chridam Over a year ago

@JibinMathew I've added the filter, check out the updated answer.

Jibin Mathew Over a year ago

the solution works only for mongo 3.2 , but the mongo i am using is 3.0.4

chridam Over a year ago

@JibinMathew Check updated answer for an alternative.

Jibin Mathew Over a year ago

if for some record in the array if the key plugin doesn't exist, that also gets returned returned, which shouldn't be the case

|

Collectives™ on Stack Overflow

Query a mongodb record comparing values in an array to the array passed in the query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related