Fetch Specific Fields from Specific Array

Question

Suppose I have a database of Patients, each of which might have more than one physical address, so the database has an Array of Addresses. I want to find a certain Patient and return only a certain field from each address in the array of Addresses.

If I use this, I successfully get every address (array member) and every detail (street, number, city, zip, etc.) of each address:

db.Patients.find( { "LastName": "Smith" }, { "Addresses": 1 } )

I try this, hoping to get only the City of the first address

db.Patients.find( { "LastName": "Smith" }, { "Addresses.0.City": 1 } )

or I try this (to simplify things), hoping to get all the details of (only) the first address

db.Patients.find( { "LastName": "Smith" }, { "Addresses.0": 1 } )

Instead, in both of those cases, I get the Patient record with an empty document for Addresses. But I don't understand why?

Thanks!

Neither do we. Perhaps you should show your document in your question. Please Edit Your Question to show the document. — Neil Lunn
– Neil Lunn, Commented Jun 18, 2017 at 13:28
Have you tried db.Patients.find( { "LastName": "Smith" }, { "Addresses.City": 1 } ) ? — s7vr
– s7vr, Commented Jun 18, 2017 at 13:41
Looking at your previous question Retrieve only some fields from sub-document which was actually renamed by myself since you were calling that structure "an array", it would seem logical that you have not learned that lesson. Something like { "Addresses": { "Home": { "street": "...", "city": "..." }, "Work": { ... } } } as I suspect you actually have, is not an array. Which explains why the notation you are using in your projection returns nothing. — Neil Lunn
– Neil Lunn, Commented Jun 18, 2017 at 13:47
Thanks Veeram! for helping me, I really learned something. If I could improve your reputation for this comment, I would. — Knom
– Knom, Commented Jun 18, 2017 at 15:30
Also Thanks to Neil! I really made some big bone-head mistakes, but with your corrections, I learn some things I didn't know before. I really appreciate it and learned a lot from it. — Knom
– Knom, Commented Jun 18, 2017 at 15:37

Tan Kim Loong · Accepted Answer · 2017-06-18 17:28:36Z

0

If want to get only the address (or its detail/children) for the particular address in the array that it matches, I don't think it's possible in MongoDB. You'll have to rely on application side matching.

If what you want to get is only a projection of a particular address field, then what you're doing is already on the right track.

The below query does not work because you're projecting the field Address.0, which does not exist in your database. You can project Address.city or Address.vicinity, but it will not return you only the one that it matches from the query.

db.Patients.find( { "LastName": "Smith" }, { "Addresses.0": 1 } )

This one will work although it will return all addresses in the document that matches your query

db.Patients.find( { "LastName": "Smith" }, { "Addresses.City": 1 } )

answered Jun 18, 2017 at 17:28

Tan Kim Loong

1,0358 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fetch Specific Fields from Specific Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related