Having difficulty formulating a MongoDB query that includes a subquery

Question

Having difficulty formulating a MongoDB query that includes a subquery

I have a dataset structured as follows:

{
  "id": "02741544",
  "items": [{
    "item": "A"
  }]
}, {
  "id": "02472691",
  "items": [{
    "item": "A"
  }, {
    "item": "B"
  }, {
    "item": "C"
  }]
}, {

  "id": "01316523",
  "items": [{
    "item": "A"
  }, {
    "item": "B"
  }]
}, {
  "id": "01316526",
  "items": [{
    "item": "A"
  }, {
    "item": "B"
  }]
}, {
  "id": "01316529",
  "items": [{
    "item": "A"
  }, {
    "item": "D"
  }]
},

I am attempting to create a query that generates an output in the following format:

{
  "item": "A",
  "ids": [{
    "id": "02741544"

  }, {
    "id": "02472691"

  }, {
    "id": "01316523"

  }, {
    "id": "01316526"

  }, {
    "id": "01316529"

  }]
}, {
  "item": "B",
  "ids": [{
    "id": "02472691"

  }, {
    "id": "01316523"

  }, {
    "id": "01316526"

  }]
}, {
  "item": "C",
  "ids": [{
    "id": "02472691"

  }]
}, {
  "item": "D",
  "ids": [{
    "id": "02472691"

  }]
},

Essentially, I am aiming to extract unique items from the item array within each object, and then create an array of ids for each object containing that item in its item array.

javascript mongodb mongodb-query aggregation-framework mongodb-aggregation

Answer 1

Answer №1

It is recommended to utilize the aggregation framework, where a series of pipeline steps need to be executed in a specific order:

$unwind - This initial step will flatten the items array, generating a copy of each document per array entry. This is important for downstream processing of documents as denormalized entities that can be grouped together.
$group - This step groups the flattened documents by the item subdocument key and creates the ids list using the $push accumulator operator.

-- UPDATE --

As highlighted by @AminJ in the comments, if items may have duplicate item values and you wish to avoid duplicate ids in the result, consider using $addToSet instead of $push.

An example illustrating this approach is:

db.collection.aggregate([   
    { "$unwind": "$items" },
    {
        "$group": {
            "_id": "$items.item",
            "ids": { 
                "$push": { "id": "$id" } /* or utilize 
                "$addToSet": { "id": "$id" } if duplicate ids should be avoided */                    
            }
        }
    }
])

Sample Output

{
    "_id" : "A",
    "ids" : [ 
        { "id" : "02741544" }, 
        { "id" : "02472691" }, 
        { "id" : "01316523" }, 
        { "id" : "01316526" }, 
        { "id" : "01316529" }
    ]
}

/* 2 */
{
    "_id" : "B",
    "ids" : [ 
        { "id" : "02472691" }, 
        { "id" : "01316523" }, 
        { "id" : "01316526" }
    ]
}

/* 3 */
{
    "_id" : "C",
    "ids" : [ 
        { "id" : "02472691" }
    ]
}

/* 4 */
{
    "_id" : "D",
    "ids" : [ 
        { "id" : "01316529" }
    ]
}

The output of an aggregate() call is a cursor pointing to the documents generated by the final stage of the aggregation pipeline. To obtain the results in an array format, you can use the cursor's toArray() method, which returns an array containing all the documents.

For instance:

var pipeline = [    
        { "$unwind": "$items" },
        {
            "$group": {
                "_id": "$items.item",
                "ids": { 
                    "$push": { "id": "$id" } /* or utilize 
                    "$addToSet": { "id": "$id" } if duplicate ids should be avoided */                   
                }
            }
        }
    ],
    results = db.collection.aggregate(pipeline).toArray();

printjson(results);

Answer 2

It is recommended to utilize the aggregation framework, where a series of pipeline steps need to be executed in a specific order:

$unwind - This initial step will flatten the items array, generating a copy of each document per array entry. This is important for downstream processing of documents as denormalized entities that can be grouped together.
$group - This step groups the flattened documents by the item subdocument key and creates the ids list using the $push accumulator operator.

-- UPDATE --

As highlighted by @AminJ in the comments, if items may have duplicate item values and you wish to avoid duplicate ids in the result, consider using $addToSet instead of $push.

An example illustrating this approach is:

db.collection.aggregate([   
    { "$unwind": "$items" },
    {
        "$group": {
            "_id": "$items.item",
            "ids": { 
                "$push": { "id": "$id" } /* or utilize 
                "$addToSet": { "id": "$id" } if duplicate ids should be avoided */                    
            }
        }
    }
])

Sample Output

{
    "_id" : "A",
    "ids" : [ 
        { "id" : "02741544" }, 
        { "id" : "02472691" }, 
        { "id" : "01316523" }, 
        { "id" : "01316526" }, 
        { "id" : "01316529" }
    ]
}

/* 2 */
{
    "_id" : "B",
    "ids" : [ 
        { "id" : "02472691" }, 
        { "id" : "01316523" }, 
        { "id" : "01316526" }
    ]
}

/* 3 */
{
    "_id" : "C",
    "ids" : [ 
        { "id" : "02472691" }
    ]
}

/* 4 */
{
    "_id" : "D",
    "ids" : [ 
        { "id" : "01316529" }
    ]
}

The output of an aggregate() call is a cursor pointing to the documents generated by the final stage of the aggregation pipeline. To obtain the results in an array format, you can use the cursor's toArray() method, which returns an array containing all the documents.

For instance:

var pipeline = [    
        { "$unwind": "$items" },
        {
            "$group": {
                "_id": "$items.item",
                "ids": { 
                    "$push": { "id": "$id" } /* or utilize 
                    "$addToSet": { "id": "$id" } if duplicate ids should be avoided */                   
                }
            }
        }
    ],
    results = db.collection.aggregate(pipeline).toArray();

printjson(results);

Answer 3

Answer №2

One way to tackle this problem is by utilizing an aggregation pipeline method:

db.collection.aggregate([
    {
        $unwind: "$items"
    },
    {
        $project: {
            id: 1,
            item: "$items.item"
        }
    },
    {
        $group: {
            _id: "$item",
            ids: {
                $push: "$id"
            }
        }
    }
])

Answer 4

One way to tackle this problem is by utilizing an aggregation pipeline method:

db.collection.aggregate([
    {
        $unwind: "$items"
    },
    {
        $project: {
            id: 1,
            item: "$items.item"
        }
    },
    {
        $group: {
            _id: "$item",
            ids: {
                $push: "$id"
            }
        }
    }
])

Having difficulty formulating a MongoDB query that includes a subquery

Answer №1

Answer №2

Similar questions

It can be frustrating to have to refresh the page twice in order to see changes when utilizing the revalidate feature in Next

JavaScript class with callback function commonly used

Error 400: The onCreate Trigger function for Cloud functions is experiencing issues with HTTP requests due to errors in the request

The JavaScript for loop using .appendChild() is inserting the values of the final object, represented as [object object], into the HTML document

Guide on exporting values from a Promise within an imported module

"Enhance your website with the powerful autocompletion feature of

Throttle the RxJs interval based on the inner observables

Switching the default image using jQuery upon click event

Prevent Float Filtering in AngularJS

To combine arrays A and B within a React useState object, simply pass them as arguments when initializing the state

Database-Driven Ajax Information Display

Show a variety of pictures in a random arrangement?

Troubleshooting the "@material-ui/core" issue in React: Step-by-step guide

I noticed that my API call is being executed twice within the router function

Using JavaScript, reload the page once the data has been retrieved from an Excel spreadsheet

Retrieve data from a JSON array using either JavaScript or PHP

Can someone help me figure out this lengthy React error coming from Material UI?

Techniques for finding the total value of a JSON array

Tips on making an array readable by JavaScript using Jinja2 and Google App Engine

How can the value be accessed when using getElementById in Angular for <mat-select> elements that do not have a value attribute?