Tuesday 6 June 2017

date based search for particular id and array of data in mongodb

schema:

/* 1 */
{
    "_id" : ObjectId("5937012cb9ee7017517a4d3c"),
    "account_number" : 12345,
    "account_name" : "John",
    "ifsc_code" : "ICIC0000003",
    "password" : "pass20",
    "currency" : "INR",
    "balance" : 920,
    "transaction" : [ 
        {
            "balance" : 1160,
            "credit" : null,
            "debit" : 40,
            "particulars" : "67891/Lisa",
            "tran_date" : ISODate("2017-06-05T18:30:00.000Z"),
            "_id" : ObjectId("593720d64850771fea133554")
        }, 
        {
            "tran_date" : ISODate("2017-06-05T18:30:00.000Z"),
            "particulars" : "67891/Lisa",
            "debit" : 40,
            "credit" : null,
            "balance" : 1120,
            "_id" : ObjectId("593724dd94b65321dfe464d6")
        }, 
        {
            "tran_date" : ISODate("2017-06-05T18:30:00.000Z"),
            "particulars" : "67891/Lisa",
            "debit" : 40,
            "credit" : null,
            "balance" : 1080,
            "_id" : ObjectId("593724ef94b65321dfe464d7")
        }, 
        {
            "tran_date" : ISODate("2017-07-05T18:30:00.000Z"),
            "particulars" : "67891/Lisa",
            "debit" : 40,
            "credit" : null,
            "balance" : 1040,
            "_id" : ObjectId("5937253194b65321dfe464d8")
        }, 
        {
            "tran_date" : ISODate("2017-04-05T18:30:00.000Z"),
            "particulars" : "67891/Lisa",
            "debit" : 40,
            "credit" : null,
            "balance" : 1000,
            "_id" : ObjectId("593725a8c91a09221a258ab6")
        }, 
        {
            "tran_date" : ISODate("2017-04-05T18:30:00.000Z"),
            "particulars" : "67891/Lisa",
            "debit" : 40,
            "credit" : null,
            "balance" : 960,
            "_id" : ObjectId("59372918b12bf7237f9bd404")
        }, 
        {
            "tran_date" : ISODate("2017-03-05T18:30:00.000Z"),
            "particulars" : "67891/Lisa",
            "debit" : 40,
            "credit" : null,
            "balance" : 920,
            "_id" : ObjectId("59372b2db12bf7237f9bd406")
        }
    ],
    "beneficiaries" : [ 
        {
            "account_number" : 67891,
            "account_name" : "Lisa"
        }
    ],
    "__v" : 1
}

/* 2 */
{
    "_id" : ObjectId("59370189b9ee7017517a4d3d"),
    "account_number" : 67891,
    "account_name" : "Lisa",
    "ifsc_code" : "ICIC0000003",
    "password" : "pass10",
    "currency" : "INR",
    "balance" : 3080,
    "transaction" : [ 
        {
            "tran_date" : ISODate("2017-04-05T18:30:00.000Z"),
            "particulars" : "12345/John",
            "debit" : null,
            "credit" : 40,
            "balance" : 3040,
            "_id" : ObjectId("59372918b12bf7237f9bd405")
        }, 
        {
            "tran_date" : ISODate("2017-03-05T18:30:00.000Z"),
            "particulars" : "12345/John",
            "debit" : null,
            "credit" : 40,
            "balance" : 3080,
            "_id" : ObjectId("59372b2db12bf7237f9bd407")
        }
    ],
    "beneficiaries" : [],
    "__v" : 0
}

i'm querying data based on from and to date

query:

db.getCollection('accounts').find({"account_number" : 12345},{"transaction.tran_date":{$gte:new Date("2017-06-02"),$lte:new Date("2017-06-03")}})

error:

Error: error: {
    "waitedMS" : NumberLong(0),
    "ok" : 0,
    "errmsg" : ">1 field in obj: { $gte: new Date(1496361600000), $lte: new Date(1496448000000) }",
    "code" : 2
}

but i'm not get from and to date based value, how to query to get exact result.



via KARTHIKEYAN.A

No comments:

Post a Comment