mongodb - find records with at least 3 populated arrays

user_16370901 0
Viewed 49

My records each have the following attributes of type Array:

alpha, beta, gamma, delta, epsilon, zeta

I know I can find records that have at least one component in an array, like this:

db.accommodations.find({'alpha.1': {$exists: true}})

I want to be able to find records that have at least one component in at least 3 arrays. For example, this record could be found:

alpha = []
beta = [1, 1, 1]
gamma = []
delta = [1, 1]
epsilon = []
zeta = [1]

But this could not:

alpha = []
beta = [1, 1, 1]
gamma = []
delta = [1, 1]
epsilon = []
zeta = []

Is there any way I can do this?

mongodb
2 Answers

Query

  • puts them in a parent array
  • filter them and keep only those that have $size>=1
    ($size is more general, if you only care for at least 1, you can also do $ne with [])
  • if the result of the filter has $size>=3 document passes

PlayMongo

aggregate(
[{"$match": 
   {"$expr": 
     {"$gte": 
       [{"$size": 
           {"$filter": 
             {"input": 
               ["$alpha", "$beta", "$gamma", "$delta", "$epsilon", "$zeta"],
              "cond": {"$gte": [{"$size": "$$this"}, 1]}}}},
        3]}}}])
user_48826920

Maybe something like this:

db.collection.aggregate([
{
"$match": {
  "$expr": {
    "$gte": [
      {
        $sum: [
          {
            "$cond": {
              "if": {
                $eq: [
                  "$alpha",
                  []
                ]
              },
              "then": 0,
              "else": 1
            }
          },
          {
            "$cond": {
              "if": {
                $eq: [
                  "$beta",
                  []
                ]
              },
              "then": 0,
              "else": 1
            }
          },
          {
            "$cond": {
              "if": {
                $eq: [
                  "$gamma",
                  []
                ]
              },
              "then": 0,
              "else": 1
            }
          },
          {
            "$cond": {
              "if": {
                $eq: [
                  "$delta",
                  []
                ]
              },
              "then": 0,
              "else": 1
            }
          },
          {
            "$cond": {
              "if": {
                $eq: [
                  "$epsilon",
                  []
                ]
              },
              "then": 0,
              "else": 1
            }
          },
          {
            "$cond": {
              "if": {
                $eq: [
                  "$zeta",
                  []
                ]
              },
              "then": 0,
              "else": 1
            }
          }
        ]
      },
      3
     ]
    }
  }
 }
])

Explained:

$match if the sum() on non empty arrays is >=3 where empty=0 and non-empty=1

playground

user_104150470
Related