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

Question

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" } }

Wernfried Domscheit · Accepted Answer · 2020-11-18 18:47:57Z

1

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

answered Nov 18, 2020 at 18:47

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

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

Comments

turivishal · Accepted Answer · 2020-11-18 18:45:22Z

1

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

answered Nov 18, 2020 at 18:45

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Comments

Sven Tenscher · Accepted Answer · 2020-11-19 10:31:35Z

1

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

answered Nov 19, 2020 at 10:31

Sven Tenscher

1952 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related