MongoDB return distinct values based on condition

user_2714150 0
Viewed 914

I am trying to use MongoDb to return distinct values for details.sub_ap_name when searching details.ap_name is equal to EVPN however my search is returning all the unique values for details.sub_ap_name

Here is what my current query looks like. I have also tried some aggregate searches as well but those are also not working for me as well. Can someonle let me know what I might be doing wrong?

db['test-data'].distinct("details.sub_ap_name",{"details.ap_name": "EVPN"})

here is my data set

{ 
    "_id" : ObjectId("101"), 
    "details" : [
        {
            "run_date" : "2021-04-18",
            "ap_name" : "EVPN",
            "sub_ap_name" : "EVPN TOR"
            
           
        },
        {
              "run_date" : "2021-04-18",
              "ap_name" : "EVPN",
              "sub_ap_name" : "EVPN Mobility"
             
             
        },
        {
            "run_date" : "2021-04-17",
            "ap_name" : "Multicast",
            "sub_ap_name" : "Multicast Layer 2"

        }
    ]
}
aggregation-framework pymongo mongodb-query mongodb
1 Answers

Demo - https://mongoplayground.net/p/49vHHUNYn8K

Note- Add index on details.ap_name for performance

$unwind

Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

$addToSet

Returns an array of all unique values that results from applying an expression to each document in a group of documents that share the same group by key. The order of the elements in the output array is unspecified.

$group

Groups input documents by the specified _id expression and for each distinct grouping outputs a document. The _id field of each output document contains the unique group by value. The output documents can also contain computed fields that hold the values of some accumulator expression.

db.collection.aggregate([
  { $match: { "details.ap_name": "EVPN" } }, // filter to reduce load on unwind
  { $unwind: "$details" }, // break into individual documents
  { $match: { "details.ap_name": "EVPN" } }, // filter
  {
    $group: { _id: "$details.ap_name", "sub_ap_names": { $addToSet: "$details.sub_ap_name" } } // add unique values
  }
])
user_22242650
Related