Mongoose query nested json array, return selected fields

Question

I am new to mongo/mongoose (using version 3.4.0), and I would like to write a query that returns a subset of a document JSON array that matches a given criteria. My document model contains an array of JSON, and I would like the query to only return the records in the array where a match occurs, and only specific fields.

Here is an example schema:

Var testSchema = Schema({
  name: { type: String },
  bag: { type: Array}  // JSON row data, containing fields name, phone, etc...
});

Example data:

name: "alpha",
bag: [ 
  { item:"apple", color:"red",   size:"small"},
  { item:"pear",  color:"white", size:"small"},
  { item:"apple", color:"green", size:"large"}
]

name: "beta",
bag: [ 
  { item:"apple", color:"brown", size:"small"},
  { item:"pear",  color:"white", size:"small"},
  { item:"apple", color:"green", size:"medium"}
]

Or

db.tests.insert({name:'alpha', bag:[{ item:'apple', color:'red', size:'small'},{ item:'pear', color:'white', size:'small'},{ item:'apple', color:'green', size:'large'}]})
db.tests.insert({name:'beta', bag:[{ item:'apple', color:'brown', size:'small'},{ item:'pear', color:'white', size:'small'},{ item:'apple', color:'green', size:'medium'}]})

I would like to be able to query this data, but only return the "bag" data that matches the query item:"apple" and only return the "item" and "color" fields from "bag".

Name: "alpha", bag: [{ item: "apple",  color: "red" }, { item: "apple",  color: "green" }]
Name: "beta", bag: [{ item: "apple",  color: "brown" }, { item: "apple",  color: "green" }]

I have tried using aggregate with match and project:

db.tests.aggregate([
    {"$match":{"bag.item":"apple"}},
    {"$project":{
        "Bag.item":{
            "$filter":{
            "input":"$bag",
            "As":"bag",
            "cond":{"$eq":["$$bag.item", "apple"]}
        }},
        "Bag.color":1
    }}]);

But this returns the color field for all items, not just apples, and still returns the size field.

I have seen these:

Mongodb Trying to get selected fields to return from aggregate

Retrieve only the queried element in an object array in MongoDB collection

but have still not figured out how to limit the items in the nested JSON array.

s7vr s7vr · Accepted Answer · 2017-03-16T23:58:08

You can try below aggregation for 3.4 version. $addFields to overwrite the existing field bag with filtered array followed by $project with exclusion to drop the size field.

db.tests.aggregate([
    {"$match":{"bag.item":"apple"}},
    {"$addFields":{
        "bag":{
            "$filter":{
            "input":"$bag",
            "as":"result",
            "cond":{"$eq":["$$result.item", "apple"]}
        }}
    }},
    {"$project":{"bag.size":0}}
]);

Mongoose query nested json array, return selected fields

1 Answers