I have a structure where I want to match the value of a field on root level with the value of a field inside another object in the same document and then choose a single document based on some condition from the result. This is the structure I have:
{
"name": "somename",
"level": "123",
"nested":[
{
"somefield": "test",
"file": {
level:"123"
}
},
{
"somefield": "test2",
"file": {
level:"124"
}
},
{
"somefield": "test3",
"file": {
level:"123"
}
}
]
}
After unwinding and matching it on a condition (level = nested.file.level) I have left with 2 documents:
[
{
"level": "123",
"name": "somename",
"nested": {
"file": {
"level": "123"
},
"somefield": "test"
}
},
{
"level": "123",
"name": "somename",
"nested": {
"file": {
"level": "123"
},
"somefield": "test3"
}
}
]
Now I want to match on somefield values, this field has 10 different values, these values are in order so if I find a matching document then I will return it or I will go to the next value in the order and check if "somefield": "orderedValue" and so on. So for example:
test
test2
test3
test4
test5
is the order and if I find a document with has "somefield": "test" I will only return that document, else I will check for "somefield": "test2" and so on until I find a single document which satisfies my condition. This is done in order so the first to satisfy the condition that the document I want.
I want to get only 1 document in the end as a result. I thought it would be best to use $switch here and wrote a project stage with $switch.
$project: {
setting: {
$switch: {
branches: [{
'case': {
$eq: [
'$nested.somefield',
'test'
]
},
then: '$nested'
},
{
'case': {
$eq: [
'$nested.somefield',
'test2'
]
},
then: '$nested'
},
{
'case': {
$eq: [
'$nested.somefield',
'test3'
]
},
then: '$nested'
}
],
'default': 'Did not match'
}
}
}
But this won't work as this would be applied on each document and if I have 5 documents with 5 of these values then it will match all of them and return the same array of documents. Any idea on how we can return only the document which matched first?
CodePudding user response：
Solution 1: With $switch operator
3rd stage: $set - Create min field and assign the value based on $switch operator.
4th stage: $sort - Order by min ascending.
5th stage: $limit - Limit to 1 document.
6th stage: $group - Group by $_id. Set the setting field by taking the first document/value with the conditions:
If the
minis lesser than or equal to 5, takenestedvalue.Else, get "Did not match" value.
db.collection.aggregate([
{
$unwind: "$nested"
},
{
$match: {
$expr: {
$eq: [
"$nested.file.level",
"$level"
]
}
}
},
{
$set: {
min: {
$switch: {
branches: [
{
"case": {
$eq: [
"$nested.somefield",
"test"
]
},
then: 1
},
{
"case": {
$eq: [
"$nested.somefield",
"test2"
]
},
then: 2
},
{
"case": {
$eq: [
"$nested.somefield",
"test3"
]
},
then: 3
},
{
"case": {
$eq: [
"$nested.somefield",
"test4"
]
},
then: 4
},
{
"case": {
$eq: [
"$nested.somefield",
"test5"
]
},
then: 5
}
],
"default": 100
}
}
}
},
{
$sort: {
min: 1
}
},
{
$limit: 1
},
{
$group: {
_id: "$_id",
setting: {
$first: {
$cond: {
if: {
$lte: [
"$min",
5
]
},
then: "$nested",
else: "Did not match"
}
}
}
}
}
])
Demo Solution 1 @ Mongo Playground
Solution 2: With $let operator
3rd stage: $set - Create min field. Declare the index variable via $let with get the array index by nested.somefield.
4th stage: $sort - Order by min ascending.
5th stage: $limit - Limit to 1 document.
6th stage: $group - Group by $_id. Set the setting field by taking the first document/value with the conditions:
If the
minis greater than or equal to 0, takenestedvalue.Else, get "Did not match" value.
db.collection.aggregate([
{
$unwind: "$nested"
},
{
$match: {
$expr: {
$eq: [
"$nested.file.level",
"$level"
]
}
}
},
{
$set: {
min: {
$let: {
vars: {
index: {
$indexOfArray: [
[
"test",
"test2",
"test3",
"test4",
"test5"
],
"$nested.somefield"
]
}
},
in: {
$cond: {
if: {
$gt: [
"$$index",
-1
]
},
then: "$$index",
else: 100
}
}
}
}
}
},
{
$sort: {
min: 1
}
},
{
$limit: 1
},
{
$group: {
_id: "$_id",
setting: {
$first: {
$cond: {
if: {
$lte: [
"$min",
4
]
},
then: "$nested",
else: "Did not match"
}
}
}
}
}
])