CODEWITHSUNDEEP
mongodbaggregation-framework
pierus
pierus

Reputation: 293

MongoDB 4.2 aggregation query to apply filter at secondary level

I'm new to mongo and doing a POC on being able to query a document for to get the locale-specific values only.
Briefly: A topLevel object has subtypes, each of which has

I want to be able to get all subtypes of a topLevel object that have a description for a specified locale (that much I can do). However, I only want to retrieve all of the altDescriptions for that locale, ignoring altDescriptions in other locales.

Ex topLevel object:

{
    "_id" : 1,
    "name" : "top level",
    "subtypes" : [ 
        {   "id" : 21,
            "name" : "child type 1",
            "description" : [ { "text" : "type 1 en_US", "locale" : "en_US" }, 
                              { "text" : "type 1 fr_FR", "locale" : "fr_FR" }, 
                              { "text" : "type 1 es_ES", "locale" : "es_ES" }],
            "altDescriptions" : [ { "name" : "type 1 alt 1 en_US", "locale" : "en_US" }, 
                                  { "name" : "type 1 alt 2 en_US", "locale" : "en_US" }, 
                                  { "name" : "type 1 alt 3 en_US", "locale" : "en_US" }, 
                                  { "name" : "type 1 alt 1 fr_FR", "locale" : "fr_FR" }]
        }, 
        {   "id" : 22,
            "name" : "child type 2",
            "description" : [ { "text" : "type 2 en_US", "locale" : "en_US"} ],
            "altDescriptions" : [ { "name" : "type 2 alt fr_FR", "locale" : "fr_FR" }]
        }, 
        {   "id" : 23,
            "name" : "child type 3",
            "description" : [ { "text" : "type 3 fr_FR", "locale" : "fr_FR"}],
            "altDescriptions" : [ { "name" : "type 3 alt en_US", "locale" : "en_US" }]
        }
    ]
}
```
My query:
```
db.topLevel.aggregate({$unwind: '$subtypes'}, {$unwind: '$subtypes.description'}, 
                      {$match: {'subtypes.description.locale' : 'en_US'}}, 
                      {$project: { type : '$subtypes'}}, {$sort: {'type.description.text' : 1}});
```
returns
```
{ "_id" : 1,
  "type" : { "id" : 21,
             "name" : "child type 1",
             "description" : { "text" : "type 1 en_US", "locale" : "en_US" },
             "altDescriptions" : [ { "name" : "type 1 alt 1 en_US", "locale" : "en_US" },
                                   { "name" : "type 1 alt 2 en_US", "locale" : "en_US" },
                                   { "name" : "type 1 alt 3 en_US", "locale" : "en_US" },
                                   { "name" : "type 1 alt 1 fr_FR", "locale" : "fr_FR" }]
           }
}
{ "_id" : 1,
  "type" : { "id" : 22,
             "name" : "child type 2",
             "description" : { "text" : "type 2 en_US", "locale" : "en_US" },
             "altDescriptions" : [{ "name" : "type 2 alt fr_FR", "locale" : "fr_FR" }]
           }
}
```

The query properly excludes 'child type 3' since it has no `en_US` description.  However, I'm not quite sure how to get it to exclude the `fr_FR` `altDescriptions `from the child type 1/2 results.  The results are otherwise what I want.

Can you please enlighten me?

Upvotes: 1

Views: 138

Answers (1)

mickl
mickl

Reputation: 49975

You can use $addFields to replace existing array and $filter to apply your condition:

{
    $addFields: {
        "type.altDescriptions": {
            $filter: {
                input: "$type.altDescriptions",
                cond: {
                    $eq: [ "$$this.locale", "$type.description.locale" ]
                }
            }
        }
    }
}

Mongo Playground

Upvotes: 2

Related Questions