MongoDB - How to use substitute value when there is missing field-and-value pair

Question

I have documents in MongoDB Atlas with this structure:

{
   "_id":"32013R0345",
   "name_lang":{
      "de":"Verordnung (EU) Nr. 345/2013 des Europäischen Parlaments und des Rates vom 17. April 2013 über Europäische Risikokapitalfonds  Text von Bedeutung für den EWR",
      "en":"Regulation (EU) No 345/2013 of the European Parliament and of the Council of 17 April 2013 on European venture capital funds  Text with EEA relevance",
      "fr":"Règlement (UE) n ° 345/2013 du Parlement européen et du Conseil du 17 avril 2013 relatif aux fonds de capital-risque européens  Texte présentant de l'intérêt pour l'EEE"
   },
   "proposedAmendments":[
      {
         "id_doc":"52016PC0461",
         "name_lang":{
            "de":"Vorschlag für eine VERORDNUNG DES EUROPÄISCHEN PARLAMENTS UND DES RATES zur Änderung der Verordnung (EU) Nr. 345/2013 über Europäische Risikokapitalfonds und der Verordnung (EU) Nr. 346/2013 über Europäische Fonds für soziales Unternehmertum",
            "en":"Proposal for a REGULATION OF THE EUROPEAN PARLIAMENT AND OF THE COUNCIL amending Regulation (EU) No 345/2013 on European venture capital funds and Regulation (EU) No 346/2013 on European social entrepreneurship funds",
            "fr":"Proposition de RÈGLEMENT DU PARLEMENT EUROPÉEN ET DU CONSEIL modifiant le règlement (UE) nº 345/2013 relatif aux fonds de capital-risque européens et le règlement (UE) nº 346/2013 relatif aux fonds d'entrepreneuriat social européens"
         }
      },
      {
         "id_doc":"52018PC0110",
         "name_lang":{
            "de":"Vorschlag für eine VERORDNUNG DES EUROPÄISCHEN PARLAMENTS UND DES RATES zur Erleichterung des grenzüberschreitenden Vertriebs von Investmentfonds und zur Änderung der Verordnungen (EU) Nr. 345/2013 und (EU) Nr. 346/2013",
            "en":"Proposal for a REGULATION OF THE EUROPEAN PARLIAMENT AND OF THE COUNCIL on facilitating cross-border distribution of collective investment funds and amending Regulations (EU) No 345/2013 and (EU) No 346/2013",
            "fr":"Proposition de RÈGLEMENT DU PARLEMENT EUROPÉEN ET DU CONSEIL visant à faciliter la distribution transfrontière des fonds communs de placement et modifiant les règlements (UE) nº 345/2013 et (UE) nº 346/2013"
         }
      },
      {
         "id_doc":"52021PC0725",
         "name_lang":{
            "en":"Proposal for a REGULATION OF THE EUROPEAN PARLIAMENT AND OF THE COUNCIL amending certain Regulations as regards the establishment and functioning of the European single access point"
         }
      }
   ]
}

I am fetching the document to display it in the frontend. In case of proposedAmendments when there is no name_lang field-and-value pair other then en (English) title I would like to create missing languages fields and to project English title as a substitute, this is important because of internalization features. The languague is set dynamiclay as ${l}. So the expected projection or aggregation of proposedAmendments for example for French should look like this:

{
   "_id":"32013R0345",
   "name_lang":{
      "fr":"Règlement (UE) n ° 345/2013 du Parlement européen et du Conseil du 17 avril 2013 relatif aux fonds de capital-risque européens  Texte présentant de l'intérêt pour l'EEE"
   },
   "proposedAmendments":[
      {
         "id_doc":"52016PC0461",
         "name_lang":{
            "fr":"Proposition de RÈGLEMENT DU PARLEMENT EUROPÉEN ET DU CONSEIL modifiant le règlement (UE) nº 345/2013 relatif aux fonds de capital-risque européens et le règlement (UE) nº 346/2013 relatif aux fonds d'entrepreneuriat social européens"
         }
      },
      {
         "id_doc":"52018PC0110",
         "name_lang":{
            "fr":"Proposition de RÈGLEMENT DU PARLEMENT EUROPÉEN ET DU CONSEIL visant à faciliter la distribution transfrontière des fonds communs de placement et modifiant les règlements (UE) nº 345/2013 et (UE) nº 346/2013"
         }
      },
      {
         "id_doc":"52021PC0725",
         "name_lang":{
            "fr":"Proposal for a REGULATION OF THE EUROPEAN PARLIAMENT AND OF THE COUNCIL amending certain Regulations as regards the establishment and functioning of the European single access point"
         }
      }
   ]
}

I have tried different combinations of MongoDB $eq, $cond and other possible expressions but without any luck. This is my basic code I need to update.

    let l = "fr";
    ...
    let doc = await collection
      .find({ _id: "32013R0345" })
      .project({
        _id: 1,
        [`name_lang.${l}`]: 1,
        [`proposedAmendments.id_doc`]: 1,
        [`proposedAmendments.name_lang.${l}`]: 1,
        ...
        ...
      })
      .toArray();

Thank everyone for any help.

user_3027266 · Answer

Try this one:

db.collection.aggregate([
   { $match: { _id: "32013R0345" } },
   // convert to { k: , v: } arrays
   {
      $set: {
         // available languages are taken from parent, not proposedAmendments, see below
         languages: { $map: { input: { $objectToArray: "$name_lang" }, in: "$$this.k" } },
         proposedAmendments: {
            $map: {
               input: "$proposedAmendments",
               as: "amendment",
               in: {
                  $mergeObjects: [
                     "$$amendment",
                     { en: "$$amendment.name_lang.en" },
                     { name_lang: { $objectToArray: "$$amendment.name_lang" } }
                  ]
               }
            }
         }
      }
   },
   // replace missing languages by English
   {
      $set: {
         "proposedAmendments": {
            $map: {
               input: "$proposedAmendments",
               as: "amendment",
               in: {
                  $mergeObjects: [
                     "$$amendment",
                     {
                        name_lang: {
                           $map: {
                              input: "$languages",
                              as: "l",
                              in: {
                                 $ifNull: [
                                    { $first: { $filter: { input: "$$amendment.name_lang", as: "lang", cond: { $eq: ["$$lang.k", "$$l"] } } } },
                                    { k: "$$l", v: "$$amendment.en" }
                                 ]
                              }
                           }
                        }
                     }
                  ]
               }
            }
         }
      }
   },
   { $unset: ["proposedAmendments.en", "languages"] },
   // convert back from { k: , v: } arrays
   {
      $set: {
         "proposedAmendments": {
            $map: {
               input: "$proposedAmendments",
               as: "amendment",
               in: {
                  $mergeObjects: [
                     "$$amendment",
                     { name_lang: { $arrayToObject: "$$amendment.name_lang" } }
                  ]
               }
            }
         }
      }
   }
])

This solution is very generic, it works for any number of languages.

If you need to retrieve the languages from proposedAmendments instead from parent then use:

languages: {
   $setUnion: {
      $reduce: {
         input: { $map: { input: { $map: { input: "$proposedAmendments", in: { $objectToArray: "$$this.name_lang" } } }, in: "$$this.k" } },
         initialValue: [],
         in: { $concatArrays: ["$$value", "$$this"] },
      }
   }
}

This part

{
   $mergeObjects: [
      "$$amendment",
      { name_lang: ... }
   ]
}

also supports generic proposedAmendments. If the proposedAmendmentsalways have only field id_doc and name_lang, then you could also use simpler

{
   id_doc: "$$amendment.id_doc",
   name_lang: ...
}

Mongo Playground

A much more static version is this one:

db.collection.aggregate([
   { $match: { _id: "32013R0345" } },
   {
      $set: {
         "proposedAmendments": {
            $map: {
               input: "$proposedAmendments",
               as: "amendment",
               in: {
                  $mergeObjects: [
                     "$$amendment",
                     {
                        name_lang: {
                           en: "$$amendment.name_lang.en",
                           de: { $ifNull: ["$$amendment.name_lang.de", "$$amendment.name_lang.en"] },
                           fr: { $ifNull: ["$$amendment.name_lang.fr", "$$amendment.name_lang.en"] }
                        }
                     }
                  ]
               }
            }
         }
      }
   }
])

Update

If you like to output only a single language, replace input: "$languages" by input: ["fr"] or replace 3rd stage by this:

  {
    $set: {
      "proposedAmendments": {
        $map: {
          input: "$proposedAmendments",
          as: "amendment",
          in: {
            $mergeObjects: [
              "$$amendment",
              {
                name_lang: [
                  {
                    $ifNull: [
                      {
                        $first: {
                          $filter: {
                            input: "$$amendment.name_lang",
                            as: "lang",
                            cond: { $eq: [ "$$lang.k", "fr" ] }
                          }
                        }
                      },
                      { k: "fr", v: "$$amendment.en" }
                    ]
                  }
                ]
              }
            ]
          }
        }
      }
    }
  },

Of course, "fr" can be replaced by your ${l}

And a solution if you prefer dynamic query compose:

var name_lang = {}
name_lang[`${l}`] = { '$ifNull': [`$$amendment.name_lang.${l}`, "$$amendment.name_lang.en"] }
db.collection.aggregate([
   { $match: { _id: "32013R0345" } },
   {
      $set: {
         "proposedAmendments": {
            $map: {
               input: "$proposedAmendments",
               as: "amendment",
               in: {
                  $mergeObjects: [
                     "$$amendment",
                     { name_lang: name_lang }
                  ]
               }
            }
         }
      }
   }
])

user_7666564 · Answer

Big thanks to Wernfried Domscheit who showed me the way. After some attempts this code seems to work for me.

let l = "fr";
let document = await db.collection.aggregate([{
            $match: {
                _id: "32013R0345"
            }
        },
        {
            $set: {
                "proposedAmendments": {
                    $map: {
                        input: "$proposedAmendments",
                        as: "proposedamendments",
                        in: {
                            $mergeObjects: [
                                "$$proposedamendments",
                                {
                                    name_lang: {
                                        [`${l}`]: {
                                            $ifNull: [
                                                `$$proposedamendments.name_lang.${l}`,
                                                "$$proposedamendments.name_lang.en",
                                            ]
                                        }
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        },
        {
            $project: {
                _id: 0,
                [`name_lang.${l}`]: 1,
                proposedAmendments: 1
            }
        }
    ])
    .toArray();

console.dir(document, {
    depth: null
});

MongoDB - How to use substitute value when there is missing field-and-value pair

2 Answers