MongoDB Multilingual - Switch Case

Question

I've recently started with MongoDB on Node.js and I need help from fellow folks on the below situation.

I've already referred Multilingual data modeling on MongoDB which is also similar Schema structure I am following.

I'm creating the list of countries in multi language format and schema as given below.

var CountrySchema = new Schema({
  name: {
    type: Object,
    required: 'Kindly enter the name of the country'
  },
  Short_code: {
    type: String,
    required: 'Please enter the country code'
  },
  Created_date: {
    type: Date,
    default: Date.now
  },
  status: {
    type: [
      {
        type: String,
        enum: ['published', 'unpublished', 'deleted']
      }
    ],
    default: ['published']
  }
})

I would like to fetch data in such a way that, when the translation is missing, it should take the default one i.e. en

The equallent SQL is given below.

SELECT Short_code, 
CASE
  WHEN name.es IS NOT NULL THEN name.es
  WHEN name.es IS NULL and name.en IS NOT NULL name.en
END AS name
FROM countries;

Also, it will be greatful if I could pass the es value dynamically to the query.

Answer 1

So let us start off with some countries in our database collection

db.countries.insertMany([
   {
       "name" : {
           "en": "Germany",
           "es": "Alemania",

       },
       "Short_code" : "DEU"
   },
   {
       "name" : {
           "en": "Spain",
           "es": "España",

       },
       "Short_code" : "ESP"
   },
   {
       "name" : {
           "en": "England"

       },
       "Short_code" : "GB"
   }
]);

Notice that DEU and ESP have Spanish translation, however GB does not.

Now we can write a simple aggregation query taking advantage of $ifNull operator (https://docs.mongodb.com/manual/reference/operator/aggregation/ifNull/) to take es value if it exists or en if not.

for example querying ESP will return the es name:

> db.countries.aggregate([
... { $match: { "Short_code" : "ESP" } },
... { $project: { "name" : { $ifNull: ["$name.es", "$name.en"] }}}
... ]);
{ "_id" : ObjectId("5b923f4b1f269cd18cd2f209"), "name" : "España" }

however querying GB will return the en name:

> db.countries.aggregate([
... { $match: { "Short_code" : "GB" } },
... { $project: { "name" : { $ifNull: ["$name.es", "$name.en"] }}}
... ]);
{ "_id" : ObjectId("5b923f4b1f269cd18cd2f20a"), "name" : "England" }

To make the es field dynamic you'd just build up the "$name.es" expression of what you want to select within your application, for example:

var userLanguage = "es";

{ $project: { "name" : { $ifNull: ["$name." + userLanguage, "$name.en"] }}}

To get only "Short_code" & "Country Name", code is

{
  $project: {
    "_id": 0,
    "Short_code": 1,
    "name" : { $ifNull: ["$name." + userLanguage, "$name.en"] }
  }
}