MongoDB query - Unwind with group and project and match and limit

0
votes

I have below type of data: 

    {
        "_id" : "HCCIDM1234567A",
        "RecordT" : "THISAPTC",
        "history" : [ 
    {
        "startDate" : ISODate("2018-01-14T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-09T11:13:14.000Z"),
        "APTCChange" : 1200,"PremChange" : 1300,"MbrRespChg" : 100,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-15T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-10T11:13:14.000Z"),
        "APTCChange" : 16,"PremChange" : 0,"MbrRespChg" : -200,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-16T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-08T11:13:14.000Z"),
        "APTCChange" : -16,"PremChange" : 0,
        "MbrRespChg" : -224,"NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-17T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-07T11:13:14.000Z"),
        "APTCChange" : 0,"PremChange" : 15,
        "MbrRespChg" : -224,"NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-18T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-06T11:13:14.000Z"),
        "APTCChange" : 0,"PremChange" : -15,
        "MbrRespChg" : -70,"NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-19T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-05T11:13:14.000Z"),
        "APTCChange" : -10,"PremChange" : -15,
        "MbrRespChg" : -77, "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-20T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-04T11:13:14.000Z"),
        "APTCChange" : 0,"PremChange" : 1,
        "MbrRespChg" : 77,"NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-12T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-23T11:13:14.000Z"),
        "PremChange" : 0,"APTCChange" : 16,
        "MbrRespChg" : -200,"NPN" : "U65"
    }
]
},
{
"_id" : "HCCIDM1234567B",
"RecordT" : "THISAPTC",
"history" : [ 
    {
        "startDate" : ISODate("2018-01-14T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-09T11:13:14.000Z"),
        "APTCChange" : 1200,
        "PremChange" : 1300,
        "MbrRespChg" : 100,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-15T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-10T11:13:14.000Z"),
        "APTCChange" : 16,
        "PremChange" : 0,
        "MbrRespChg" : -200,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-16T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-08T11:13:14.000Z"),
        "APTCChange" : -16,
        "PremChange" : 0,
        "MbrRespChg" : -224,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-17T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-07T11:13:14.000Z"),
        "APTCChange" : 0,
        "PremChange" : 15,
        "MbrRespChg" : -224,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-18T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-06T11:13:14.000Z"),
        "APTCChange" : 0,
        "PremChange" : -15,
        "MbrRespChg" : -70,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-19T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-05T11:13:14.000Z"),
        "APTCChange" : -10,
        "PremChange" : -15,
        "MbrRespChg" : -77,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-20T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-04T11:13:14.000Z"),
        "APTCChange" : 0,
        "PremChange" : 1,
        "MbrRespChg" : 77,
        "NPN" : "U65"
    }, 
    {
        "startDate" : ISODate("2018-01-12T11:13:14.000Z"),
        "endDate" : ISODate("2018-02-23T11:13:14.000Z"),
        "PremChange" : 0,
        "APTCChange" : 16,
        "MbrRespChg" : -200,
        "NPN" : "U65"
    }
]
}, 
{
    "_id" : "HCCIDM1234567C",
    "RecordT" : "THISAPTC",
    "history" : [ 
        {
            "startDate" : ISODate("2018-01-14T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-09T11:13:14.000Z"),
            "APTCChange" : 1200,
            "PremChange" : 1300,
            "MbrRespChg" : 100,
            "NPN" : "U65"
        }, 
        {
            "startDate" : ISODate("2018-01-15T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-10T11:13:14.000Z"),
            "APTCChange" : 16,
            "PremChange" : 0,
            "MbrRespChg" : -200,
            "NPN" : "U65"
        }, 
        {
            "startDate" : ISODate("2018-01-16T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-08T11:13:14.000Z"),
            "APTCChange" : -16,
            "PremChange" : 0,
            "MbrRespChg" : -224,
            "NPN" : "U65"
        }, 
        {
            "startDate" : ISODate("2018-01-17T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-07T11:13:14.000Z"),
            "APTCChange" : 0,
            "PremChange" : 15,
            "MbrRespChg" : -224,
            "NPN" : "U65"
        }, 
        {
            "startDate" : ISODate("2018-01-18T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-06T11:13:14.000Z"),
            "APTCChange" : 0,
            "PremChange" : -15,
            "MbrRespChg" : -70,
            "NPN" : "U65"
        }, 
        {
            "startDate" : ISODate("2018-01-19T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-05T11:13:14.000Z"),
            "APTCChange" : -10,
            "PremChange" : -15,
            "MbrRespChg" : -77,
            "NPN" : "U65"
        }, 
        {
            "startDate" : ISODate("2018-01-20T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-04T11:13:14.000Z"),
            "APTCChange" : 0,
            "PremChange" : 1,
            "MbrRespChg" : 77,
            "NPN" : "U65"
        }, 
        {
            "startDate" : ISODate("2018-01-12T11:13:14.000Z"),
            "endDate" : ISODate("2018-02-23T11:13:14.000Z"),
            "PremChange" : 0,
            "APTCChange" : 16,
            "MbrRespChg" : -200,
            "NPN" : "U65"
        }
    ]
}

Below conditions needs to apply:

  1. startDate $gte ISODate('2018-01-15T11:13:14.000Z') & endDate $lte ISODate('2018-02-12T11:13:14.000Z')

  2. need to group them based on below category

    PremChange>10- records will in one set with limit
    APTCChange>10- records will in one set with limit MbrRespChg>10- records will in one set with limit

Expected result: 

    {
       id: APTCChange,
       "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567A',
      'startDate': ISODate("2018-01-14T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-09T11:13:14.000Z"),
      "APTCChange" : 1200,
      "PremChange" : 1300,
      "MbrRespChg" : 100,
      "NPN" : "U65"
     }
     -------- 
     {
       id: APTCChange,
       "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567A',
      'startDate': ISODate("2018-01-15T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-08T11:13:14.000Z"),
      "APTCChange" : 16,
      "PremChange" : 0,
      "MbrRespChg" : -200,
       "NPN" : "U65"
     }
     ---------
     {
       id: PremChange,
      "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567A',
      'startDate': ISODate("2018-01-16T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-07T11:13:14.000Z"),
      "APTCChange" : 0,
      "PremChange" : 15,
      "MbrRespChg" : -224,
      "NPN" : "U65"
     } 
    -----
   {
       id: MbrRespChg,
       "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567A',
      'startDate': ISODate("2018-01-18T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-04T11:13:14.000Z"),
      "APTCChange" : 0,
      "PremChange" : 1,
      "MbrRespChg" : 77,
      "NPN" : "U65"
     }
     ---
     {
       id: APTCChange,
       "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567B',
      'startDate': ISODate("2018-01-14T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-09T11:13:14.000Z"),
      "APTCChange" : 1200,
      "PremChange" : 1300,
      "MbrRespChg" : 100,
      "NPN" : "U65"
     }
     -------- 
     {
       id: APTCChange,
       "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567B',
      'startDate': ISODate("2018-01-15T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-08T11:13:14.000Z"),
      "APTCChange" : 16,
      "PremChange" : 0,
      "MbrRespChg" : -200,
       "NPN" : "U65"
     }
     ---------
     {
       id: PremChange,
      "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567B',
      'startDate': ISODate("2018-01-16T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-07T11:13:14.000Z"),
      "APTCChange" : 0,
      "PremChange" : 15,
      "MbrRespChg" : -224,
      "NPN" : "U65"
     } 
    -----
   {
       id: MbrRespChg,
       "RecordT" : "THISAPTC",
       "hccid':'HCCIDM1234567B',
      'startDate': ISODate("2018-01-18T11:13:14.000Z"),
      "endDate" : ISODate("2018-02-04T11:13:14.000Z"),
      "APTCChange" : 0,
      "PremChange" : 1,
      "MbrRespChg" : 77,
      "NPN" : "U65"
     }

please suggest me how i need to write mongodb query, i wrote a
query using match, unwind and project but i am not getting desire output.

1
jsonmongodbaggregation-framework
The data you've provided is malformed and badly formatted for a question. You should be using a "pretty" format. Additionally, you haven't described the problem or shown any steps you've taken to try to solve it. We can't do anything with the small amount of information you've provided. – B. Fleming
I’ve tried to edit your OP to help make it readable, but there’s too much code so it won’t let me edit it! Please provide what query you’ve used and what your end result you’re after is – user9251303
i am sorry, i am trying to give proper data format of json but in this site, keep on giving an error to make proper code format, but i am just copying from mongo and pasted, still it is giving same error. – Sanjay
all possible ways i have tried to post properly and clearly – Sanjay

1 Answers

0
votes

You can try below aggregation. Not sure why you need grouping here.

$match to filter histories array to where atleast one element is in the specified date range.

$unwind to flatten the history array.

$match to filter history documents where element matches the specified date range.

$match to filter history documents where key values are greater than 10. 

db.col.aggregate([
  {"$match":{
  "history":{
    "$elemMatch":{
      "startDate":{"$gte":ISODate("2018-01-15T11:13:14.000Z")},
      "endDate":{"$lte":ISODate("2018-02-12T11:13:14.000Z")}
     }
   }
 }},
 {"$unwind":"$history"},
 {"$match":{
   "history.startDate":{"$gte":ISODate("2018-01-15T11:13:14.000Z")}, 
   "history.endDate":{"$lte":ISODate("2018-02-12T11:13:14.000Z")}
 }},
 {"$match":{
   "$or":[
     {"history.APTCChange":{"$gt":10}},
     {"history.PremChange":{"$gt":10}},
     {"history.MbrRespChg":{"$gt":10}}
    ]
 }}
])