Search Exact Array Values In Multiple Fields

Question

I have a collection which has 3 documents like below:

Collection:
{
    name: "A",
    arr: [1, 2, 3],
    arr1: [4, 5, 6]
},
{
    name: "B",
    arr: [3, 7, 11],
    arr1: [5, 6, 9]
},
{
    name: "C",
    arr: [3, 4, 5],
    arr1: [7, 9, 12]
}

I want to search array below in the collection.
But all array values must be matched in fields "arr" or "arr1".
I mean array values can be in either fields but all values must be in the document.
So when I search array in the collection only second which has name:"B" and third which has name:"C" documents should be the result.
Because in the second document; first array value( 3 ) in the "arr" field and second and third array values(5 and 9) in the "arr1" field. In the third document first and second (3, 5) array values in the "arr" field and third array value (9) in the "arr1" field.

Array : [3, 5, 9]

Can you help me?

your question is too complicated to understand can you explain more details? — Yogesh
I actually gave all the details but I can summarize it. I have a collection and I want to search an array in this collection. Result documents should include all array values. There is one condition, array values can be elements either in "arr" field or "arr1" field. I gave an example above, result will be document 2 and document 3. Is there any query that will give this result? — baris usanmaz

styvane styvane · Accepted Answer · 2015-06-03T08:45:33

The best way to do this is using the $redact operator.

db.collection.aggregate([
    { "$redact": {
        "$cond": [
            { "$setIsSubset": [ [3,5,9], { "$setUnion": [ "$arr", "$arr1" ] } ] }, 
            "$$KEEP", 
            "$$PRUNE" 
         ]}
    }
])

You can also use $project with the $setUnion operator and $match.

db.collection.aggregate([
    { "$project": { "name": 1, "arr": 1, "arr1": 1, "allvalues": { "$setUnion": [ "$arr", "$arr1" ]}}}, 
    { "$match": { "allvalues": { "$all": [3, 5, 9] }}}
])

Output:

{ "_id" : ObjectId("55d48fd2939d0f7d372d6dbe"), "name" : "B", "arr" : [ 3, 7, 11 ], "arr1" : [ 5, 6, 9 ] }
{ "_id" : ObjectId("55d48fd2939d0f7d372d6dbf"), "name" : "C", "arr" : [ 3, 4, 5 ], "arr1" : [ 7, 9, 12 ] }

Search Exact Array Values In Multiple Fields

1 Answers