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? — Neo-coder
– Neo-coder, Commented Jun 3, 2015 at 8:16
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
– baris usanmaz, Commented Jun 3, 2015 at 8:26

Sede · Accepted Answer · 2015-08-19 14:39:40Z

7

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 ] }

edited Aug 19, 2015 at 14:39

answered Jun 3, 2015 at 8:45

Sede

61.5k20 gold badges158 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

Search Exact Array Values In Multiple Fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related