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?
CodePudding user response:
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
aggregate(
[{"$match":
{"$expr":
{"$gte":
[{"$size":
{"$filter":
{"input":
["$alpha", "$beta", "$gamma", "$delta", "$epsilon", "$zeta"],
"cond": {"$gte": [{"$size": "$$this"}, 1]}}}},
3]}}}])
CodePudding user response:
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