Filter by another Collection's field

How would I go about filtering a set of records based on their child records.

Let’s say I have a collection Item that has a field to another collection Bag called bagId. I’d like to find all Items where a field on Bags matches some clause.

I.e. db.Items.find( { "where bag.type:'Paper' " }) . How would I go about doing this in MongoDB. I understand I’d have to join on Bags and then link where Item.bagId == Bag._id

I used Studio3T to convert a SQL GROUP BY to a Mongo aggregate. I’m just wondering if there’s any defacto way to do this.

  1. Should I perform a data migration to simply include Bag.type on every Item document (don’t want to get into the habit of continuously making schema changes everytime I want to sort/filter Items by Bag fields).

  2. Use something like (No luck with that syntax yet)

  3. Grapher I played around with this briefly and while it’s cool I’m not sure if it’ll actually solve my problem. I can use it to add Bag.type to every Item returned, but I don’t see how that could help me filter every item by Bag.type.

Is this just one of the tradeoffs of using a NoSQL dbms? What option above is recommended or are there any other ideas?


Not sure if you need something more complex but looking at your example, the question is whether that is a 1 to 1 relation. If an Item can only have one bag, and the bag can be of many kinds, you’d rather just write the type of bag on the Item. (You 1. case)
You would indeed need to aggregate if your bag changed type with time, or color or other properties (Your case 2.) but if your bag.type could very well live in a fixture like Json and not suffer changes, you could just write it in the Item as a direct property of the item.

I take it you are more familiar with SQLs and if yes, I’d suggest you read the differences between SQL and NoSQL.
An example related to your example:
I have two collections: Players and Users. John is a player, has blue eyes and a userId. Since John’s eyes color won’t change soon, I write the color on both Players and Users for the fastest filtering on both collections if I need all players with blue eyes as well as users with blue eyes. With NoSQL, if applicable, you write the same info in multiple places. This is specific to document based DB architecture as opposed to table.
It really depends on how you consume data.