How to Count Matched array elements

Question

I have a collection and each document in that collection has an array field countries. I want to select all documents which include any of below countries:

China, USA, Australia

And the output should show the number of above countries each document has. I use below aggregate command:

db.movies.aggregate([
  {
    $match: { countries: { $in: ["USA", 'China', 'Australia'] } }
  },
  {
    $project: {
      countries: {$size: '$countries'}
    }
  }
]);

it doesn't work as expected. It shows the number of all countries in the document who has the above-listed country. For example, if a document has China, Japan in its countries field, I expected it return 1 (because only China is in the above country list) but it returns two. How can I do that in the aggregation command?

Neil Lunn · Accepted Answer · 2017-10-21 05:22:25Z

The $in operator just "queries" documents that contain one of the possible values, so it does not remove anything from the array.

If you want to count "only matches" then apply $setIntersection to the array before $size:

db.movies.aggregate([
  {
    $match: { countries: { $in: ["USA", 'China', 'Australia'] } }
  },
  {
    $project: {
      countries: {
        $size: { 
         "$setIntersection": [["USA", 'China', 'Australia'], '$countries' ] 
       }
    }
  }
]);

That returns the "set" of "unique" matches to the array provided against the array in the document.

There is an alternate of $in as an aggregation operator in modern releases ( MongoDB 3.4 at least ). This works a bit differently in "testing" a "singular" value against an array of values. In array comparison you would apply with $filter:

db.movies.aggregate([
  {
    $match: { countries: { $in: ["USA", 'China', 'Australia'] } }
  },
  {
    $project: {
      countries: {
        $size: { 
         $filter: {
           input: '$countries',
           cond: { '$in': [ '$$this', ["USA", 'China', 'Australia'] ] }
         }
       }
    }
  }
]);

That really should only be important to you where the array "within the document" contains entries that are not unique. i.e:

{ countries: [ "USA", "Japan", "USA" ] }

And you needed to count 2 for "USA", as opposed to 1 which would be the "set" result of $setIntersection

Collectives™ on Stack Overflow

How to Count Matched array elements

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related