0

I have a collection called member_transaction. The sample document structure has given below:

{
    "memberId": "XY-123",
    "transactionsDetails": {
        "2015-07-15": [
            {
                "memberId": "1011",
                "orderId": "232",
                "topLevelcategory": "household",
                "orderValue": "34313.200"
            },
            {
                "memberId": "1011",
                "orderId": "235",
                "topLevelcategory": "Furniture",
                "orderValue": "2534.200"
            }
        ],
        "2015-07-16": [
            {
                "memberId": "1011",
                "orderId": "235",
                "topLevelcategory": "Furniture",
                "orderValue": "2534.200"
            },
            {
                "memberId": "1012",
                "orderId": "235",
                "topLevelcategory": "Grocery",
                "orderValue": "2534.200"
            },
            {
                "memberId": "1013",
                "orderId": "235",
                "topLevelcategory": "Grocery",
                "orderValue": "2534.200"
            },
            {
                "memberId": "1014",
                "orderId": "235",
                "topLevelcategory": "Grocery",
                "orderValue": "2534.200"
            }
        ],
        "2015-07-18": [
            {
                "memberId": "2011",
                "orderId": "1232",
                "topLevelcategory": "household",
                "orderValue": "34313.200"
            },
            {
                "memberId": "103",
                "orderId": "2352",
                "topLevelcategory": "Furniture",
                "orderValue": "2534.200"
            }
        ],
        "2015-07-19": [
            {
                "memberId": "311",
                "orderId": "152",
                "topLevelcategory": "household",
                "orderValue": "34313.200"
            },
            {
                "memberId": "1013",
                "orderId": "2312",
                "topLevelcategory": "Furniture",
                "orderValue": "2534.200"
            },
            {
                "memberId": "1043",
                "orderId": "2316",
                "topLevelcategory": "Grocery",
                "orderValue": "2534.200"
            },
            {
                "memberId": "1017",
                "orderId": "2315",
                "topLevelcategory": "Furniture",
                "orderValue": "2544"
            }
        ]
    }
}

How to query a mongoDB having array structure between two key, i.e. how do I extract the data between the key "2015-07-16" & "2015-07-19" from the above document ?

Improve this question

1 Answer 1

Reset to default
1

It would be pretty difficult to query such a schema that has dynamic keys. A better and recommended approach is to redesign the schema in such a way that you have an embedded document that has a date key that holds the date value and the transactionDetails key that stores the array. The following demonstrates the ideal schema change:

db.member_transaction.insert({
    "memberId": "XY-123",
    "transactions": [
        {
            "date": ISODate("2015-07-15"),
            "transactionsDetails": [
                {
                    "memberId": "1011",
                    "orderId": "232",
                    "topLevelcategory": "household",
                    "orderValue": "34313.200"
                },
                {
                    "memberId": "1011",
                    "orderId": "235",
                    "topLevelcategory": "Furniture",
                    "orderValue": "2534.200"
                }
            ]
        },
        {
            "date": ISODate("2015-07-16"),
            "transactionsDetails": [
                {
                    "memberId": "1011",
                    "orderId": "235",
                    "topLevelcategory": "Furniture",
                    "orderValue": "2534.200"
                },
                {
                    "memberId": "1012",
                    "orderId": "235",
                    "topLevelcategory": "Grocery",
                    "orderValue": "2534.200"
                },
                {
                    "memberId": "1013",
                    "orderId": "235",
                    "topLevelcategory": "Grocery",
                    "orderValue": "2534.200"
                },
                {
                    "memberId": "1014",
                    "orderId": "235",
                    "topLevelcategory": "Grocery",
                    "orderValue": "2534.200"
                }
            ]
        },
        {
            "date": ISODate("2015-07-18"),
            "transactionsDetails": [
                {
                    "memberId": "2011",
                    "orderId": "1232",
                    "topLevelcategory": "household",
                    "orderValue": "34313.200"
                },
                {
                    "memberId": "103",
                    "orderId": "2352",
                    "topLevelcategory": "Furniture",
                    "orderValue": "2534.200"
                }
            ]
        },
        {
            "date": ISODate("2015-07-19"),
            "transactionsDetails": [
                {
                    "memberId": "311",
                    "orderId": "152",
                    "topLevelcategory": "household",
                    "orderValue": "34313.200"
                },
                {
                    "memberId": "1013",
                    "orderId": "2312",
                    "topLevelcategory": "Furniture",
                    "orderValue": "2534.200"
                },
                {
                    "memberId": "1043",
                    "orderId": "2316",
                    "topLevelcategory": "Grocery",
                    "orderValue": "2534.200"
                },
                {
                    "memberId": "1017",
                    "orderId": "2315",
                    "topLevelcategory": "Furniture",
                    "orderValue": "2544"
                }
            ]
        }
    ]
})

With the schema above you can then use the aggregation framework to query your documents with that date range. The following aggregation pipeline will achieve the desired result:

db.member_transaction.aggregate([
    {
        "$match": {
            "transactions.date": {
                "$gte": ISODate("2015-07-16T00:00:00.000Z"),
                "$lte": ISODate("2015-07-19T00:00:00.000Z")
            }
        }
    },
    {
        "$unwind": "$transactions"
    },
    {
        "$match": {
            "transactions.date": {
                "$gte": ISODate("2015-07-16T00:00:00.000Z"),
                "$lte": ISODate("2015-07-19T00:00:00.000Z")
            }
        }
    },
    {
        "$group": {
            "_id": null,
            "memberId": { "$first": "$memberId" },
            "transactions": { "$push": "$transactions" }
        }
    }
])

Sample Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : null,
            "memberId" : "XY-123",
            "transactions" : [
                {
                    "date" : ISODate("2015-07-16T00:00:00.000Z"),
                    "transactionsDetails" : [ 
                        {
                            "memberId" : "1011",
                            "orderId" : "235",
                            "topLevelcategory" : "Furniture",
                            "orderValue" : "2534.200"
                        }, 
                        {
                            "memberId" : "1012",
                            "orderId" : "235",
                            "topLevelcategory" : "Grocery",
                            "orderValue" : "2534.200"
                        }, 
                        {
                            "memberId" : "1013",
                            "orderId" : "235",
                            "topLevelcategory" : "Grocery",
                            "orderValue" : "2534.200"
                        }, 
                        {
                            "memberId" : "1014",
                            "orderId" : "235",
                            "topLevelcategory" : "Grocery",
                            "orderValue" : "2534.200"
                        }
                    ]
                }, 
                {
                    "date" : ISODate("2015-07-18T00:00:00.000Z"),
                    "transactionsDetails" : [ 
                        {
                            "memberId" : "2011",
                            "orderId" : "1232",
                            "topLevelcategory" : "household",
                            "orderValue" : "34313.200"
                        }, 
                        {
                            "memberId" : "103",
                            "orderId" : "2352",
                            "topLevelcategory" : "Furniture",
                            "orderValue" : "2534.200"
                        }
                    ]
                }, 
                {
                    "date" : ISODate("2015-07-19T00:00:00.000Z"),
                    "transactionsDetails" : [ 
                        {
                            "memberId" : "311",
                            "orderId" : "152",
                            "topLevelcategory" : "household",
                            "orderValue" : "34313.200"
                        }, 
                        {
                            "memberId" : "1013",
                            "orderId" : "2312",
                            "topLevelcategory" : "Furniture",
                            "orderValue" : "2534.200"
                        }, 
                        {
                            "memberId" : "1043",
                            "orderId" : "2316",
                            "topLevelcategory" : "Grocery",
                            "orderValue" : "2534.200"
                        }, 
                        {
                            "memberId" : "1017",
                            "orderId" : "2315",
                            "topLevelcategory" : "Furniture",
                            "orderValue" : "2544"
                        }
                    ]
                }
            ]
        }
    ],
    "ok" : 1
}

UPDATE

You could use the $elemMatch projection operator which limits the contents of an field from the query results but not that only the first element matching the $elemMatch condition will be returned (i.e. the transactionDetails array document with date "2015-07-16"), thus the aggregation framework is the preferred approach as it returns all the desired result.

The following nonetheless demonstrates how the $elemMatch operator works in your example:

db.member_transaction.find(
    {
        "transactions.date": {
            "$gte": ISODate("2015-07-16T00:00:00.000Z"),
            "$lte": ISODate("2015-07-19T00:00:00.000Z")
        }
    }, 
    {
        "memberId": 1,
        "transactions": {
            "$elemMatch": {
                "date": {
                    "$gte": ISODate("2015-07-16T00:00:00.000Z"),
                    "$lte": ISODate("2015-07-19T00:00:00.000Z")
                }
            }
        }
    }
)

Sample Output:

/* 0 */
{
    "_id" : ObjectId("55bf84307b4720b3bcd0b9ab"),
    "memberId" : "XY-123",
    "transactions" : [ 
        {
            "date" : ISODate("2015-07-16T00:00:00.000Z"),
            "transactionsDetails" : [ 
                {
                    "memberId" : "1011",
                    "orderId" : "235",
                    "topLevelcategory" : "Furniture",
                    "orderValue" : "2534.200"
                }, 
                {
                    "memberId" : "1012",
                    "orderId" : "235",
                    "topLevelcategory" : "Grocery",
                    "orderValue" : "2534.200"
                }, 
                {
                    "memberId" : "1013",
                    "orderId" : "235",
                    "topLevelcategory" : "Grocery",
                    "orderValue" : "2534.200"
                }, 
                {
                    "memberId" : "1014",
                    "orderId" : "235",
                    "topLevelcategory" : "Grocery",
                    "orderValue" : "2534.200"
                }
            ]
        }
    ]
}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi Chridam, Without using aggregation can we extract the data between date range in your structure??
@SanjayakumarSahu It's possible through the $elemMatch projection operator which limits the contents of an <array> field from the query results to contain only the first element matching the $elemMatch condition.
Could you please share a sample query & its output using $elemMatch in the above structure ?
@SanjayakumarSahu I've updated the answer to include the $elemMatch operator and its limitation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.