CODEWITHSUNDEEP
javascriptmongodbmongodb-queryaggregation-framework
user6148078
user6148078

Reputation: 711

Query one field equal to another array field?

Suppose the collection like this:

{
   movie : 1,
   List : [ 1 , 2 ,5 , 6 ]
},
{
   movie : 2,
   List : [ 3, 5, 7 ]
},
{
   movie : 3,
   List : [ 1, 3, 6 ]
}

I want get all documents that 'movie' exist in the 'List'.

How do I write the query or aggregate?

Upvotes: 4

Views: 140

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151072

The ideal form of this is with native operators using .aggregate() with $redact:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$setIsSubset": [
          { "$map": { "input": ["A"], "as": "el", "in": "$movie" } },
          "$List"
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Or if you don't have $redact available in your MongoDB version, use the $where query condition instead: 

db.collection.find(function() {
  return this.List.indexOf(this.movie) != 1
})

Both have the basic approach of looking for the value of one field being present within the array field in the document.

There are a couple of different forms you can use with $redact, such as this $anyElementTrue call:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$anyElementTrue": {
          "$map": {
            "input": "$List",
            "as": "el",
            "in": { "$eq": [ "$$el", "$movie" ] }
          }
        }
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

As well as a shorter syntax of the original with MongoDB 3.2:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$setIsSubset": [
          ["$movie"],
          "$List"
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Where just as with the usage of $map originally the ["$movie"] make the single element an array/set for which you compare using $setIsSubset. In the latter case the $map is just applying a condition to each element of the array to return an array of true/false values which is then reduced to a logical single true/false by $anyElementTrue.

Upvotes: 1

Related Questions