CODEWITHSUNDEEP
mongodbmongodb-query
JPG
JPG

Reputation: 1253

how to use query and group by in mongodb

I was trying to count documents in a collection, group by date based on one query. Following is the sample document:

{
    "_id" : ObjectId("5d2b5c4a33ad6154e776658b"),
    "_class" : "com.abc.mongo.docs.OCSMongoLog",
    "jsonObject" : {
            "keyIdentifier" : "218XXXXXXX25",
            "microOperationName" : "Deduct amount from user balance.",
            "moduleName" : "OCS",
            "responseRaw" : "<?xml version=\"1.0\" encoding=\"UTF-8\"?><soapenv:Envelope xmlns:soapenv=\"http://schema
                            s.xmlsoap.org/soap/envelope/\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema
                            -instance\"><soapenv:Body><AcceptDebbitUnitResponse xmlns=\"http://abc.xyz.ws.bss.ijk.google.com\"><AcceptUnitResp>
                            <ConversationID>101XXVVXX2111-testing</ConversationID><TransactionID>560XXCC273</TransactionID>
                            <Amount>1</Amount></AcceptUnitResp></AcceptDebbitUnitResponse></soapenv:Body></soapenv:Envelope>",
            "operationName" : "Direct Debit",
            "id" : null,
            "createDate" : "2019-07-14 12:46:59.456",
            "requestRaw" : "DebitBalance [conversationId=1012ZXXXXZZ916, transactionId=, originatingAddress=21894
                            7XXZZX25, destinationAddress=218XXXZZZ5, chargingAddress=21XXXZZZ25, amount=1]"
    }

}

What I am trying

I want to count the number of documents based on group by createDate where jsonObject.responseRaw contains characters <Amount>

I tried following query :

db.oCSMongoLog.find( { $query: {"jsonObject.responseRaw" : {$regex : "<Amount>"}}}, $group: { "jsonObject.createDate" :
-1 } } ).count();

I got syntax error in this.. can anyone help me out for this case.

What I expecting

something like: 

{
"2019-07-14" : count,
"2019-07-15" : count,
"2019-07-16" : count,
"2019-07-17" : count,
}

Upvotes: 1

Views: 46

Answers (1)

Nikhil Savaliya
Nikhil Savaliya

Reputation: 2166

You can use aggregate method to get similar output,

db.oCSMongoLog.aggregate([
    {
        $match: {
            "jsonObject.responseRaw": { $regex: "<Amount>" }
        }
    }, {
        $group: {
            _id: { $dateToString: { format: "%Y-%m-%d", date: "$jsonObject.createDate" } },
            count: { $sum: 1 }
        }
    }])

Output:

/* 1 */
{
    "_id" : "2019-07-14",
    "count" : 1
},

/* 2 */
{
    "_id" : "2019-07-15",
    "count" : 1
},

/* 3 */
{
    "_id" : "2019-07-26",
    "count" : 2
}

Upvotes: 2

Related Questions