'Filtering based on a date within a matching object in an array of objects
We have a collection of documents that look like this:
The timeline holds different events that happened with a particular object. I am trying to find a way to filter objects based on the date within the timeline object with status CREATED.
If I understand that correctly, I have to do it through aggregations. One possible article in the direction is this: https://docs.mongodb.com/manual/reference/operator/aggregation/first-array-element/
The way I am thinking about it is to add this matching timeline item as an additional field and match on that.
Is there an easier (faster) way to achieve it?
The result I am looking for is a collection of documents within a certain month based on the date field in the CREATED item under a timeline.
So, every document has an object under timeline with a status CREATED. That object has a date field. I need to filter the collection by that.
Then run a bunch of aggregates on the filtered collection.
EDIT - Adding more data
Full Document
{
"_id": {
"$oid": "55762fbf86c273233aebc101"
},
"from": {
"nickname": "Savings",
"type": "TYPE-US",
"id": "5574edcc86c27322ef865ea4",
"user": {
"legal_names": [
"Some new name"
],
"_id": "557387ed86c27318532fc09a"
}
},
"extra": {
"ip": "192.168.0.1",
"supp_id": "1283764wqwsdd34wd13212",
"webhook": "http://requestb.in/1acojwy1",
"process_on": {
"$date": "2015-06-10T00:13:51.263Z"
},
"note": "Deposit",
"created_on": {
"$date": "2015-06-09T00:13:51.263Z"
},
"other": {
"skip_validation": false,
"do_next": true
},
"latlon": "0,0",
"same_day": false
},
"timeline": [
{
"date": {
"$date": "2015-06-09T00:13:51.263Z"
},
"status": "CREATED",
"note": "Transaction created",
"status_id": "1"
},
{
"status": "CANCELED",
"note": "",
"datetime": {
"$date": "2015-06-25T00:37:00.515Z"
},
"status_id": "-1"
},
{
"date": {
"$date": "2015-07-07T07:16:44.756Z"
},
"status": "CREATED",
"note": "Transaction unqueued",
"status_id": "1"
},
{
"date": {
"$date": "2015-07-07T07:16:44.809Z"
},
"status": "PROCESSING-DEBIT",
"note": "Transaction debit being processed.",
"status_id": "2"
},
{
"date": {
"$date": "2015-07-07T07:16:44.853Z"
},
"status": "PROCESSING-CREDIT",
"note": "Transaction credit being processed.",
"status_id": "3"
},
{
"date": {
"$date": "2015-07-07T07:16:47.051Z"
},
"status": "SETTLED",
"note": "Transaction credit has been made.",
"status_id": "4"
}
],
"to": {
"nickname": "ACH-US",
"type": "ACH-US",
"id": "5574d23b86c27354b2db471e",
"user": {
"legal_names": [
"Some new name"
],
"_id": "557387ed86c27318532fc09a"
}
},
"amount": {
"currency": "cUSD",
"amount": 1010
},
"client": {
"id": 844,
"name": "Bob Joe"
},
"fees": [
{
"note": "Company Facilitator Fee",
"to": {
"id": "559339aa86c273605ccd35df"
},
"fee": 25
},
{
"note": "Facilitator Fee",
"to": {
"id": "559339aa86c273605ccd35df"
},
"fee": 100
}
],
"recent_status": {
"date": {
"$date": "2015-07-07T07:16:47.051Z"
},
"status": "SETTLED",
"note": "Transaction credit has been made.",
"status_id": "4"
},
"_v": 2
}
Solution 1:[1]
So, based on my understanding, you can have more than one CREATED in the timeline, but if that entry exists for the month you are inquiring about the document is valid for retrieval. Here is an aggregation which uses $elemMatch to find these documents...
Enterprise atlas-7aocnr-shard-0 [primary]> db.collection.find( { timeline: { $elemMatch: { status: "CREATED", $and: [ { date: { $gte: ISODate("2015-06-01") } }, { date: { $lt: ISODate("2015-07-01") } } ] } } } )
[
{
_id: ObjectId("55762fbf86c273233aebc101"),
from: {
nickname: 'Savings',
type: 'SYNAPSE-US',
id: '5574edcc86c27322ef865ea4',
user: {
legal_names: [ 'Some new name' ],
_id: '557387ed86c27318532fc09a'
}
},
extra: {
ip: '192.168.0.1',
supp_id: '1283764wqwsdd34wd13212',
webhook: 'http://requestb.in/1acojwy1',
process_on: 2015-06-10T00:13:51.263Z,
note: 'Deposit to bank account',
created_on: 2015-06-09T00:13:51.263Z,
other: { skip_validation: false, do_next: true },
latlon: '0,0',
same_day: false
},
timeline: [
{
date: 2015-06-09T00:13:51.263Z,
status: 'CREATED',
note: 'Transaction created',
status_id: '1'
},
{
status: 'CANCELED',
note: '',
datetime: 2015-06-25T00:37:00.515Z,
status_id: '-1'
},
{
date: 2015-07-07T07:16:44.756Z,
status: 'CREATED',
note: 'Transaction unqueued',
status_id: '1'
},
{
date: 2015-07-07T07:16:44.809Z,
status: 'PROCESSING-DEBIT',
note: 'Transaction debit being processed.',
status_id: '2'
},
{
date: 2015-07-07T07:16:44.853Z,
status: 'PROCESSING-CREDIT',
note: 'Transaction credit being processed.',
status_id: '3'
},
{
date: 2015-07-07T07:16:47.051Z,
status: 'SETTLED',
note: 'Transaction credit has been made.',
status_id: '4'
}
],
to: {
nickname: 'ACH-US',
type: 'ACH-US',
id: '5574d23b86c27354b2db471e',
user: {
legal_names: [ 'Some new name' ],
_id: '557387ed86c27318532fc09a'
}
},
amount: { currency: 'cUSD', amount: 1010 },
client: { id: 844, name: 'Sankaet Pathak' },
fees: [
{
note: 'Synapse Facilitator Fee',
to: { id: '559339aa86c273605ccd35df' },
fee: 25
},
{
note: 'Facilitator Fee',
to: { id: '559339aa86c273605ccd35df' },
fee: 100
}
],
recent_status: {
date: 2015-07-07T07:16:47.051Z,
status: 'SETTLED',
note: 'Transaction credit has been made.',
status_id: '4'
},
_v: 2
}
]
Solution 2:[2]
If you want to filter only the "CREATED" objects inside the timeline array , this is the way to go:
mongos> db.z.aggregate([
{
$project:{
timelines:{
$filter:{
input:"$timeline" ,
as:"item" ,
cond: { $eq:["$$item.status","CREATED" ] }
}
}
}
} ]).pretty()
{
"_id" : {
"oid" : "55762fbf86c273233aebc101"
},
"timelines" : [
{
"date" : {
"$date" : "2015-06-09T00:13:51.263Z"
},
"status" : "CREATED",
"note" : "Transaction created",
"status_id" : "1"
},
{
"date" : {
"$date" : "2015-07-07T07:16:44.756Z"
},
"status" : "CREATED",
"note" : "Transaction unqueued",
"status_id" : "1"
}
]
}
mongos>
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | barrypicker |
| Solution 2 | R2D2 |