I have a problem getting a result with a query in the MongoDB shell. Apparently, the structure of the information stored is not as complex, and neither should the query, but I have not. Also, I have to be careful with the processing time, since I have thousands of thousands of records, and launching myself into a very complex query would take me a lot of response time. The stored documents have this structure:
{
"_id" : ObjectId("5b0ed238d921d920a0997b9a"),
"id" : "ABC",
"className" : "Processing",
"error" : false,
"registerDate" : ISODate("2018-05-30T11:32:31.000-05:00")
}
{
"_id" : ObjectId("5b0ed239d921d920a0997b9b"),
"id" : "PQR",
"className" : "Processing",
"error" : false,
"registerDate" : ISODate("2018-05-30T11:32:40.000-05:00")
}
{
"_id" : ObjectId("5b0ed239d921d920a0997b9c"),
"id" : "XYZ",
"className" : "Processing",
"error" : true,
"registerDate" : ISODate("2018-05-30T11:32:40.000-05:00")
}
{
"_id" : ObjectId("5b11c233f3bad457380ef33a"),
"id" : "ABC",
"senderClass" : "Sender",
"senderError" : false,
"registerDate" : ISODate("2018-06-01T17:01:22.000-05:00")
}
{
"_id" : ObjectId("5b11c233f3bad457380ef33b"),
"id" : "PQR",
"senderClass" : "Sender",
"senderError" : true,
"registerDate" : ISODate("2018-06-01T17:01:22.000-05:00")
}
{
"_id" : ObjectId("5b14c2jh3kk43k4j80ef15js"),
"id" : "PQR",
"senderClass" : "Sender",
"senderError" : false,
"registerDate" : ISODate("2018-06-01T17:03:72.000-07:00")
}
In the collection, two types of documents are stored: processing and sending.
- Each event processed, has an ID, but in case it fails once, it can retry up to 3 times with the same ID. Therefore, they can There are 3 records with the same ID
- Once processed it will be sent, and if the shipment fails it can
Retry up to 3 times and also use the same ID. As in the
example: the PQR event is processed once without error (error = false) and sent 2 times (the first with senderError = true and the second one
with senderError = false). - If the event reaches the limit of retries in processing without success, will not be sent (as in the case of the XYZ event).
In the query that I need to ask, I want to obtain a result of this type:
{
"_id" : "ABC",
"date" : ISODate("2018-06-01T17:03:31.000-05:00"),
"failedSend" : false //No falló envío
}
{
"_id" : "PQR",
"date" : ISODate("2018-06-01T17:03:36.000-05:00"),
"failedSend" : false //No falló envío
}
{
"_id" : "XYZ",
"date" : ISODate("2018-06-01T17:32:40.000-05:00"),
"failedSend" : true //Si falló envío
}
Where I was told if the event was finally sent or not . There are two ways to know that the document was not sent:
- Failed several times in the processing and did not get sent. (It will have the field error = true)
- It did not fail in processing, but repeated times in the shipment. Y in these conditions the SI event is sent:
- It was processed well (either at first or n retries) and then sent well (in the first or n retries). (As in the case of the event with id PQR, the first attempt to send does not work but the second one does ).
I have built the following query:
db.trace.aggregate([
{
$match :
{
$and :
[
{registerDate : { $gt: new Date( '05/15/2018' ) } },
//Aquí no se como construir el filtro para que me liste los eventos que fallaron o los que no
]
}
},
{
$group : {
_id : "$id",
date: {$last : "$registerDate"},
sendStates : { $push : "$senderError" }
}
},
{
$skip : 0
},
{
$limit : 50
},
{
$project:
{
date: 1,
failedSend :
{
$cond: {
if: { $allElementsTrue: [ "$sendStates" ] },
then: true,
else: false
}
}
}
}
]
)
As you will see, the query has two parameters to make the filter:
- The date from which you wish to obtain the data
- Whether or not the mail is sent.
The query seems to work without the second filter (it returns me which ones were sent and which ones were not from the date indicated), but I want to return only those that were sent or those that were not, depending on the parameter that I passed him. And that already takes me two days and I do not achieve :(
Additional note: I think the query would improve if I store the nested documents, however I can not change the storage structure of the documents . A part that are already many, that make the query that I have built takes almost a minute :( Then I'll have to pass the whole query to Java, but first I want to make sure it works in the Mongo shell.
Porfa help :( thanks!