How to get all elements for which at least one deeply nested object has an array which contains a certain value?

[Ujlm (Ulysse M)]

Hi there, hope you’re doing well. I come from relational databases, but I’d like to try out MongoDB.
However, I’m stuck on how to query the following scenario:

I have 10 friends and they all have apple trees. Apples belong to different varieties, and have different colors and sizes.
I want to store all data on the apples, and I want to be able to query for
- Apple size
- Apple color
- Apple variety
- Any combination of these properties
If a friend has a certain color of a certain apple, (s)he can have no, one or multiple sizes of it (hence the “sizes” array).

Currently, my data is modelled as follows:

{
	_id: ObjectId
	variety: string
	colors: [
		{
			color: string,
            owner: [
                {
                    friendName: string,
                    sizes: string[]
                }
            ]
        }
	]
}

Here is a scenario to illustrate my problem:

[
{
	_id: ...
	variety: 'mcintosh'
	colors: [
		{
			color: 'light red',
            owner: [
                {
                    friendName: 'jennifer',
                    sizes: [1, 2 , 6]
                }
            ]
        },
        		{
			color: 'dark red',
            owner: [
                {
                    friendName: 'steve',
                    sizes: [6, 7]
                }
            ]
        }
	]
},
{
	_id: ...
	variety: 'granny smith'
	colors: [
		{
			color: 'light green',
            owner: [
                {
                    friendName: 'jennifer',
                    sizes: [2 , 4]
                }
            ]
        },
        		{
			color: 'speckled dark green',
            owner: [
                {
                    friendName: 'jonathan',
                    sizes: [3, 7]
                }
            ]
        }
	]
}
]

Now I want to get all Apples of size 3 (in this case only the speckled dark green one of jonathan).
Which query should I form so that it returns the granny smith object?
I try to get something like “select all varieties where there is at least one color where the owner has a size 3”.

There is probably a better way to model this in the database, would you suggest a different way to structure this?

[Simon_Bieri (traceurSaimn)]

I hope I did understand your problem correctly as there is a quite easy way of doing this.

Try this query:

db = db.getSiblingDB(“<your DB>”);
db.getCollection(“>your Collection>”).find(
{
“colors.owner.sizes” : 3.0
}
);

This should return your document.

[Ujlm (Ulysse M)]

Thank you very much! This solves the problem indeed.
I knew the dot notation existed, but I thought it only worked for exact equalities.