Retrieving items from an array within a MongoDB aggregate operation based on specific criteria for fields at the same level

After analyzing the data provided, I am seeking a way to retrieve objects from the od array based on specific conditions. Additionally, I aim to extract the em and name fields as well.

Although I lack extensive knowledge on MongoDB's aggregate functionality. Therefore, assistance is needed to resolve my current dilemma.

{
_id: 1,
em: '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="85e4e7e6c5b4b7f6abebe0f1">[email protected]</a>',
name: 'NewName',
od: 
[
    {
        "oid": ObjectId("1234"),
        "ca": ISODate("2016-05-05T13:20:10.718Z")
    },
    {
        "oid": ObjectId("2345"),
        "ca": ISODate("2016-05-11T13:20:10.718Z")
    },
    {
        "oid": ObjectId("57766"),
        "ca": ISODate("2016-05-13T13:20:10.718Z")
    }
]       
},
{
_id: 2,
em: '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="96f7f4a0f5d6eeefecb8f8f3e2">[email protected]</a>',
name: 'NewName2',
od: 
[
    {
        "oid": ObjectId("1234"),
        "ca": ISODate("2016-05-11T13:20:10.718Z")
    },
    {
        "oid": ObjectId("2345"),
        "ca": ISODate("2016-05-12T13:20:10.718Z")
    },
    {
        "oid": ObjectId("57766"),
        "ca": ISODate("2016-05-05T13:20:10.718Z")
    }
]       
}

My attempts with $match, $project, and $unwind of aggregate have been made to achieve the desired outcome. The query used is as follows:

db.collection.aggregate([
{
    $match : {
                    "od.ca": {
                        '$gte': '10/05/2016',
                        '$lte': '15/05/2016'
                    }
                }
},
{
    $project:{
                    _id: '$_id',
                    em: 1,
                    name: 1,
                    od: 1
                }
},
{
    $unwind: "$od"
}, 
{
    $match: {
                    "od.ca": {
                        '$gte': '10/05/2016',
                        '$lte': '15/05/2016'
                    }
                }
}])

The result obtained includes em, name, and an od array with one object from od, indicating multiple records for the same email ID.

 
// Result Output Examples
{
_id: 1,
em: '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="fa9b9899bacbc889d4949f8e">[email protected]</a>',
name: 'NewName',
od: 
[
{
    "oid": ObjectId("57766"),
    "ca": ISODate("2016-05-13T13:20:10.718Z")
}
]       
}
{
_id: 1,
em: '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="066764654637347528686372">[email protected]</a>',
name: 'NewName',
od: 
[
{
    "oid": ObjectId("2345"),
    "ca": ISODate("2016-05-11T13:20:10.718Z")
}
]       
}

To achieve the desired output format where each unique email ID contains all matching objects in the od array, modifications may be required in the query structure. An example of the expected result is shown below:

 
// Desired Output Format Example
{
_id: 1,
em: '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b5d4d7d6f58487c69bdbd0c1">[email protected]</a>',
name: 'NewName',
od: 
[
    {
        "oid": ObjectId("2345"),
        "ca": ISODate("2016-05-11T13:20:10.718Z")
    },
    {
        "oid": ObjectId("57766"),
        "ca": ISODate("2016-05-13T13:20:10.718Z")
    }
]       
}

If the query is not generating the expected results, further analysis or adjustments within the query structure are recommended to achieve the desired outcome. Any insights or guidance on improving the query would be greatly appreciated.

javascriptmongodbmongodb-queryaggregation-framework

Answer №1

If your MongoDB version is newer than 2.6.X, you don't really need a cohort of aggregation operators except for specific cases. The $filter operator can handle the job effectively.

For instance, when using the $project stage along with the $filter operator, you can filter the 'od' array to include only documents with 'ca' dates between '2016-05-10' and '2016-05-15':

var start = new Date('2016-05-10'),
    end = new Date('2016-05-15');

db.collection.aggregate([
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },
    {
        "$project": {
            "em": 1,
            "name": 1,
            "od": {
                "$filter": {
                    "input": "$od",
                    "as": "o",
                    "cond": {  
                        "$and": [
                            { "$gte": [ "$$o.ca", start ] },
                            { "$lte": [ "$$o.ca", end ] }
                        ]
                    }
                }
            }
        }
    }
])

Keep in mind that this operator is available for MongoDB versions 3.2.X and above.

However, for versions ranging from 2.6.X to 3.0.X, you can utilize a combination of the $map and $setDifference operators to simulate a filtering mechanism for documents within the 'ca' array.

The $map operator essentially maps values based on conditions evaluated by the $cond operator to either false or true outcomes. Subsequently, the $setDifference operator calculates the set difference from the previous results. Refer to the example below:

var start = new Date('2016-05-10'),
    end = new Date('2016-05-15');

db.collection.aggregate([
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },
    {
        "$project": {
            "em": 1,
            "name": 1,
            "od": {
                "$setDifference": [
                    {
                        "$map": {
                            "input": "$od",
                            "as": "o",
                            "in": {
                                "$cond": [
                                    {  
                                        "$and": [
                                            { "$gte": [ "$$o.ca", start ] },
                                            { "$lte": [ "$$o.ca", end ] }
                                        ]
                                    },
                                    "$$o",
                                    false
                                ]
                            }
                        }
                    },
                    [false]
                ]
            }
        }
    }
])

For versions prior to 2.4.X, you may have to employ a concoction of the $match, $unwind, and $group operators if the aforementioned ones are not supported.

In the following example, these operators work together to group all matched documents from the unwound result back into their original schema, excluding the filtered array elements:

db.collection.aggregate([
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },  
    { "$unwind": "$od" },
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },
    {
        "$group": {
            "_id": "$_id",
            "em": { "$first": "$em" },
            "name": { "$first": "$name" },
            "od": { "$push": "$od" }
        }
    }
])

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

I encountered the error message "TypeError: e is undefined" while attempting to make an ajax call

My goal is to showcase a website's content using file_get_contents in a PHP script and ajax on the front-end. I am able to display the entire page successfully, but when attempting to only show a certain number of images, I encounter the "TypeError: e ...

javascriptjqueryajaxfile-get-contents

Encountering difficulties while implementing the AJAX request with the Giphy API

I am just getting started with jQuery and JavaScript. My goal with the ajax or get method is to input any keyword (like maine coon, for example), hit submit, and then see a page full of maine coon gifs. The API code I am working with is from Giphy. func ...

javascriptjqueryajaxgetgiphy-api

Creating a flexible parent tag for grouping child elements using Angular 2

My Objective I am determined to develop an Angular 2 button component that can dynamically render as either an <a /> tag or an <input /> tag based on the value of a property called type. Furthermore, this button component should be able to acc ...

javascriptangular

Passing data from an API in Vue.js to a different page

I'm a beginner with Vue Js and I'm looking for guidance on how to send data between two components. Currently, I am working on a Vue app that fetches a list of users from an API and displays them. My goal is to transfer data between these compone ...

javascriptvue.jsvue-componentvue-routervuejs3

Getting the most out of the fastest-validator package: Implementing multiple patterns and custom messages

Utilizing the powerful fastest-validator library for validation purposes. I have a requirement where the password field must contain at least one character and one number. Along with this, I am in need of specific messages for validating these conditions ...

javascriptnode.jsregexexpressvalidation

Is the number of 'Restarts in the past hour' alerts for the MongoDB Atlas dedicated cluster a cause for concern?

Within our specialized cluster using an M10 Atlas replicaset with three nodes on Mongo 6.0.3, hosted on AWS, we've set up an alert for 'Restarts in last hour is' rule if it exceeds 0 for any node. https://www.mongodb.com/docs/atlas/referenc ...

mongodbmongodb-atlas

Styling is applied by Bootstrap to inputs in a form that are not required

I am currently working on validating a form for empty fields using Bootstrap. When submitting and validating the form with the form.checkValidity() method, I noticed that even the non-required fields are being styled as if they are required. Is this normal ...

javascripthtmlbootstrap-4

Saving only the date (excluding the time) into MongoDB using Mongoose

I'm attempting to add a document with the current date. Here's what I have tried so far: query const collectionSchema = new Schema({ date: new Date() }); const data = mongoose.model('data', collectionSchema); When I insert data ...

node.jsmongodbmongoose

Utilizing JSON for the setAttribute() method

While I've made progress on this issue, I've encountered numerous confusing methods. The goal is to utilize the key:value pairs in attr{} for the setAttribute() function without relying on any framework. If anyone could provide a straightforward ...

javascriptjson

exit out of React Dialog using a button

I have a scenario where I want to automatically open a dialog when the screen is visited, so I set the default state to true. To close the dialog, I created a custom button that, when clicked, should change the state to false. However, the dialog does no ...

javascriptreactjstypescriptdialogmaterial-ui

The issue with Three.js responsive canvas is that it fails to properly adjust the size of the

I'm currently working on a threejs basic scene and attempting to create a responsive canvas for a full-screen experience. However, the mesh inside the scene is not resizing correctly as expected. Instead of remaining a cube, it distorts into a rectang ...

javascriptcsscanvasthree.jsresize

How to bind array elements in Vue.js

I am currently working with an array that contains various arrays and properties within it. My goal is to iterate through the array and generate new rows for each item in it. Here is a snippet of what I have been working on: var orderDetails = [ ...

javascriptvue.jsdata-binding

Receiving the error message "Encountered issue reading property of 0" while attempting to retrieve a specific element from an array

Currently, I am facing an issue where I am trying to access a value that is set in one function within another function. When I attempt to return this value at the end, the console.log displays the value correctly. However, when I try to set it, I receive ...

javascriptexpress

Hapi/Node.js Reference Error for Request: Troubleshooting the Issue

I am facing an issue with my two API handlers: module.exports.loginWeb = function (request, reply, next) { if (request.auth.isAuthenticated) { return reply.redirect('/'); } if(request.method === 'get'){ rep ...

javascriptnode.jshapi.js

Using Rxjs for MongoDB Document References: A Step-by-Step Guide

Currently, I am working on an app using Ionic2/rc0. In my singleton service, I have a ReplaySubject that maintains the consistency of the current user throughout the entire application. Everything is functioning properly, and I can easily subscribe to it a ...

mongodbangularionic2rxjs5

Troubleshooting Problem with Facebook Messenger Bot Webhook

I received the following notification: Your Webhooks subscription callback URL is not currently accepting updates. We have observed that your Webhooks subscription for callback URL has been inactive for at least 16 minutes. Please ensure that your cal ...

javascriptjsonnode.jsfacebookfacebook-graph-api

Is it possible to retrieve the vertices array from a QuickHull instance in three.js?

I'm currently working on generating a geometry using QuickHull from a THREE Mesh. However, it seems that the QuickHull object only contains information pertaining to the Faces of the mesh. Does anyone know if there is a way to access the vertex infor ...

javascriptthree.js3d

It never fails to function automatically, no matter which script is being executed

By default, the script will always be executed regardless of its environment. Check out my code snippet: import { Pool } from 'pg'; import config from './../config'; const connectionString = () => { switch (process.env.NODE_EN ...

javascriptnode.js

nodejs express routing issue resulting in not found error

I recently started a new node project and wanted to enhance my route adding capabilities. In the past, I only went one level deep with folders, but this time I wanted to go further. To achieve this, I created a recursive function that adds routes and navig ...

javascriptnode.jsrecursionexpress

Checking to see if all the users mentioned in the message have been assigned a role

Hello everyone, I'm new to asking questions here so please bear with me. I am trying to retrieve all the users mentioned in a message and check if any of them have a specific role, such as the staff role. Thank you for your help! Edit: Here is the ...

javascriptdiscord.js