Querying localized data in MongoDB

Question

Having data 'localized' like this:

{
  "cs": [
    {
      "cID": "00001",
      "title": {
        "en": "title en1",
        "de": "title de1"
      },
      "desc": {
        "en": "desc en1",
        "de": "desc de1"
      },
      "startDate": 20210801,
      "endDate": 20210809,
      "numDays": 8
    },
    {
      "cID": "00002",
      "title": {
        "en": "title en2",
        "de": "title de2"
      },
      "desc": {
        "en": "desc en2",
        "de": "desc de2"
      },
      "startDate": 20210701,
      "endDate": 20210715,
      "numDays": 14
    }
  ]
}

What would be the best way to query this taking user locale (passed to query as param) into account? For example, if "en" was passed, the query should return this for "cID": "00001" :

    {
      "cID": "00001",
      "title": "title en1",
      "desc": "desc en1",
      "startDate": 20210801,
      "endDate": 20210809,
      "numDays": 8
    }

Ideally, the query should be 'generic' and not filter specifically the 'title' and 'desc' objects.

I know that:

db.cs.find({
  "cID": "00001"
},
{
  "_id": 0,
  "cID": 1,
  "title": "$title.en",
  "desc": "$desc.en",
  "startDate": 1,
  "endDate": 1,
  "numDays": 1
})

will give me:

[
  {
    "cID": "00001",
    "desc": "desc en1",
    "endDate": 2.0210809e+07,
    "numDays": 8,
    "startDate": 2.0210801e+07,
    "title": "title en1"
  }
]

but it would be tricky to handle it with many locales and different data models in different queries.

Mongo playground: https://mongoplayground.net/p/9erh-VYiOO4

Answer 1

You can try,

• $reduce to iterate loop of title array, $cond to match local and return match result to value, same process for description array

 {
    $addFields: {
      title: {
        $reduce: {
          input: "$title",
          initialValue: "",
          in: {
            $cond: [{ $eq: ["$$this.locale", "pl"] }, "$$this.value", "$$value"]
          }
        }
      },
      description: {
        $reduce: {
          input: "$description",
          initialValue: "",
          in: {
            $cond: [{ $eq: ["$$this.locale", "pl"] }, "$$this.value", "$$value"]
          }
        }
      }
    }
  }

for the generic option you can make a function with two parameters first one is input field with $ sign and second one is locale:

function languageFilter(inputField, locale) {
  return {
    $reduce: {
      input: inputField,
      initialValue: "",
      in: {
        $cond: [{ $eq: ["$$this.locale", locale] }, "$$this.value", "$$value"]
      }
    }
  };
}

Your final query would be:

let locale = "pl";
db.cs.aggregate([
  { $match: { cID: "00001" } },
  {
    $addFields: {
      title: languageFilter("$title", locale)
      description: languageFilter("$description", locale)
    }
  }
]);