CODEWITHSUNDEEP
mongodbmongodb-query
Sven Tenscher
Sven Tenscher

Reputation: 195

MongoDB Query based on value in array and return a single field

I have a collection called countries:

{
    "_id" : "123456",
    "enabled" : true,
    "entity" : {
            "name" : [
                    {
                            "locale" : "en",
                            "value" : "Lithuania"
                    },
                    {
                            "locale" : "de",
                            "value" : "Litauen"
                    }
            ]
    }
}

I like to return only the ObjectId and the value when the locale is "en".

 {"_id":"123456", "value":"Lithuania"}

Ideally renaming value to country for:

 {"_id":"123456", "country":"Lithuania"}

Using a projection like:

db.countries.aggregate([
    {$project: 
        {country: {$arrayElemAt:["$entity.name",0]}}
    }
])

returns almost the desired results:

 {"_id" : "1234565", "country" : { "locale" : "en", "value" : "Lithuania" } }

Upvotes: 0

Views: 67

Answers (3)

Sven Tenscher
Sven Tenscher

Reputation: 195

I should have specified the MongoDB Version. Server is running 3.6.20. For that the following solution works:

db.countries.aggregate([
  {
    $addFields: {
      country: {
        $filter: {
          input: "$entity.name",
          cond: { $eq: [ "$$this.locale", "en" ] }
        }
      }
    }
 },
  { $project: { country: { $arrayElemAt: [["$country.value"],0] } } },
])

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

This this one:

db.collection.aggregate([
  {
    $set: {
      country: {
        $filter: {
          input: "$entity.name",
          cond: { $eq: [ "$this.locale", "en" ] }
        }
      }
    }
  },
  { $project: { country: { $first: "$country.value" } } },
])

See Mongo playground

Upvotes: 1

turivishal
turivishal

Reputation: 36134

You can try,

  • $reduce to iterate loop of entity.name array, $cond check locale is "en" then return value
db.collection.aggregate([
  {
    $project: {
      country: {
        $reduce: {
          input: "$entity.name",
          initialValue: "",
          in: {
            $cond: [
              { $eq: ["$$this.locale", "en"] },
              "$$this.value",
              "$$value"
            ]
          }
        }
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions