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.

what is your mongodb server version ?

s7vr
– s7vr

2017-03-16 23:50:39 +00:00
Commented Mar 16, 2017 at 23:50 — s7vr
– s7vr, Commented Mar 16, 2017 at 23:50
db.version() is 3.4.0

J21042
– J21042

2017-03-16 23:56:41 +00:00
Commented Mar 16, 2017 at 23:56 — J21042
– J21042, Commented Mar 16, 2017 at 23:56

s7vr · Accepted Answer · 2017-03-16 23:58:08Z

1

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}}
]);

answered Mar 16, 2017 at 23:58

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

Collectives™ on Stack Overflow

Mongoose query nested json array, return selected fields

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