Mongodb query array element meet criteria

Question

I have a document like this:

solution:{
   "name":"solution name",
   "desc":"description",
   "case":[
          {
          "A":13,
          "B":"aaaa"
          },
          {
          "A":14,
          "B":"aaaa"
          },
          {
          "A":13,
          "B":"bbbb"
          }
   ]
}

The case is an array field in solution table, and it contains two fields, field A and filed B.

Now I can query the solution record, and the return result will include all case elements. Now I wish the query result only includes case elements which filed B is "aaaa", how to write the query in java or MongoDB?

My expected query result should be like this:

solution:{
   "name":"solution name",
   "desc":"description",
   "case":[
          {
          "A":13,
          "B":"aaaa"
          },
          {
          "A":14,
          "B":"aaaa"
          },
   ]
}

Can you post your java code what you have tried? Also, do you use spring data mongodb? — Vasu, Mar 18 '17 at 07:54
Possible duplicate of [How to filter array in subdocument with MongoDB](http://stackoverflow.com/questions/15117030/how-to-filter-array-in-subdocument-with-mongodb) — s7vr, Mar 18 '17 at 10:35

radhakrishnan · Accepted Answer · 2017-03-18T09:25:39.387

You can use aggregate pipeline $redact to keep only matched objects,

Mongo shell command,

    db.solution.aggregate([ {$redact: {
        "$cond": [{

                "$eq": [{
                        "$ifNull": ["$B", "aaaa"]
                    },
                    "aaaa"
                ]

        }, "$$DESCEND", "$$PRUNE"]
    }}]).pretty()

Mongo Java code,

        MongoClient client = new MongoClient("localhost");
        MongoDatabase db = client.getDatabase("Test"); 
        MongoCollection<Document> collection = db.getCollection("solution");
        List<Document> results = collection.aggregate(Arrays.asList(
              new Document("$redact", new Document("$cond",
                      Arrays.asList(new Document("$eq",
                              Arrays.asList(new Document("$ifNull", Arrays.asList("$B", "aaaa")), "aaaa")), 
                      "$$DESCEND", "$$PRUNE")))

        )).into(new ArrayList<Document>());

        for(Document docs: results){
          System.out.println(docs.toJson());
        }

Verified your answer on mongodb command. I need to understand it, thanks very much, very useful. — Kimi Liu, Mar 20 '17 at 01:49
we can add condition: db.solution.aggregate([ {$match:{"name":"solution name"}}, {$redact: { "$cond": [{ "$eq": [{ "$ifNull": ["$B", "aaaa"] }, "aaaa" ] }, "$$DESCEND", "$$PRUNE"] }}]).pretty() — Kimi Liu, Mar 20 '17 at 01:59

score 0 · Answer 2 · answered Mar 18 '17 at 15:27

You may use $filter aggregation to filter an array based on a condition during projection

db.solution.aggregate([
    {$match: {'case.B': "aaaa"}},
    {$project: {
        case: {$filter: {
            input: '$case',
            as: 'case',
            cond: {$eq: ['$$case.B', "aaaa"]}
        }},
        name:1,
        desc:1
    }}
])

Mongodb query array element meet criteria

2 Answers2