[ { "$match": { "startDate": { "$gte": { "$date": "2018-03-12T03:26:21.997Z" }, "$lte": { "$date": "2024-03-10T04:50:35Z" } } } }, { "$lookup": { "from": "participant", "localField": "userId", "foreignField": "_id", "as": "participantData" } }, { "$unwind": "$participantData" }, { "$match": { "participantData.email": { "$regularExpression": { "pattern": "@jiva.user", "options": "" } } } }, { "$match": { "participantData.userStatus": { "$ne": "TEST" } } }, { "$match": { "$and": [ { "participantData.email": { "$not": { "$regularExpression": { "pattern": "deleted", "options": "" } } } }, { "participantData.email": { "$not": { "$regularExpression": { "pattern": "@smit\\.fit$", "options": "" } } } } ] } }, { "$lookup": { "from": "participantBaselineAndFollowupData", "localField": "userId", "foreignField": "participantId", "as": "baselineData" } }, { "$unwind": "$baselineData" }, { "$lookup": { "from": "blood_glucose", "localField": "userId", "foreignField": "participantId", "as": "glucoseData" } }, { "$unwind": "$glucoseData\", \"preserveNullAndEmptyArrays\" : true}" }, { "$project": { "subscriptionId": "$_id", "baselineId": "$baselineData._id", "userId": 1, "startDate": 1, "programStartDate": "$baselineData.programStartDate", "planCode1": "$subscriptionPlan.programCode", "journeyStatus": "$journeyTrackerObject.status", "planCode2": "$baselineData.programCode", "followUps": "$baselineData.followUps", "glucoseData": 1 } }, { "$match": { "$and": [ { "$expr": { "$and": [ { "$eq": [ "$planCode1", "$planCode2" ] }, { "$eq": [ "$startDate", "$programStartDate" ] } ] } } ] } }, { "$group": { "_id": { "userId": "$userId", "startDate": "$startDate", "planCode1": "$planCode1" }, "userId": { "$last": "$userId" }, "baselineId": { "$last": "$baselineId" }, "startDate": { "$last": "$startDate" }, "programStartDate": { "$last": "$programStartDate" }, "subscriptionId": { "$last": "$subscriptionId" }, "journeyStatus": { "$last": "$journeyStatus" }, "followUps": { "$last": "$followUps" }, "glucoseData": { "$push": "$glucoseData" } } }, { "$project": { "userId": 1, "baselineId": 1, "startDate": 1, "programStartDate": 1, "subscriptionId": 1, "journeyStatus": 1, "followUps": 1, "field": { "$add": [ "$_id.startDate", { "$multiply": [ 14, 24, 60, 60, 1000 ] } ] }, "glucoseData": { "$filter": { "input": "$glucoseData", "as": "glucose", "cond": { "$and": [ { "$lt": [ "$$glucose.date", { "$add": [ "$_id.startDate", { "$multiply": [ 14, 24, 60, 60, 1000 ] } ] } ] }, { "$gte": [ "$$glucose.date", "$_id.startDate" ] } ] } } } } }, { "$project": { "userId": 1, "baselineId": 1, "startDate": 1, "programStartDate": 1, "subscriptionId": 1, "journeyStatus": 1, "followUps": 1, "glucoseData": { "$map": { "input": "$glucoseData", "as": "obj", "in": { "participantId": "$$obj.participantId", "reading": "$$obj.reading", "date": "$$obj.date" } } } } } ]
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter