MongoDB: Retrieve value from embedded document, without parent document-CodePudding

Right now I have the current documents:

[
  {
    "name": "John",
    "state": "CA",
    "people": [
      {
        "id": "1",
        "qty": "5",
        "type": "3"
      },
      {
        "id": "4",
        "qty": "5",
        "type": "6"
      }
    ]
  },
  {
    "name": "Katie",
    "state": "NY",
    "people": [
      {
        "id": "434",
        "qty": "5",
        "type": "63"
      },
      {
        "id": "34",
        "qty": "6",
        "type": "21"
      }
    ]
  }
]

And what I want to have is a query that retrieves just the name, id and qty, with some query condition on id (here qty = 5), in the following format (no parent document 'people' included) :

[{"name": "John", "id": "1", "qty": "5",},
{"name": "John", "id": "4", "qty": "5",},
{"name": "Katie", "id": "434", "qty": "5",}]

I have this query:

db.collection.find( { "qty": "5"}, { "name": 1,"people.id": 1, "people.qty": 1});

But this returns the parent document, i.e gives me this:

{ name: 'John',
  people: { id: '1', qty: '5'} }

Any ideas how to query so I get the desired output?

CodePudding user response：

Query

uwind people to get each member in different document
filter to get only qty=5
project to un-nest and get the structure you want

*if you have a multikey index on people.qty you can add this also as first stage
{"$match": {"people.qty": "5"}}

Test code here

aggregate(
[{"$unwind": {"path": "$people"}}, 
 {"$match": {"people.qty": "5"}},
 {"$project": 
   {"_id": 0, "name": 1, "id": "$people.id", "qty": "$people.qty"}}])