I have a 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.
CodePudding user response：
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: ...
}
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 }
]
}
}
}
}
}
])
CodePudding user response：
Big thanks to Wernfried Domscheit who showed my the way. After some attempts this code seems to work for me.
let l = "fr";
let document = db.collection.aggregate([{
$match: {
_id: "32013R0345"
}
},
{
$set: {
"proposedAmendments": {
$map: {
input: "$proposedAmendments",
as: "amendment",
in: {
$mergeObjects: [
"$$amendment",
{
name_lang: {
[`${l}`]: {
$ifNull: [
`$$amendment.name_lang.${l}`,
"$$amendment.name_lang.en",
]
}
}
}
]
}
}
}
}
},
{
$project: {
_id: 0,
[`name_lang.${l}`]: 1,
proposedAmendments: 1
}
}
])
.toArray();
console.dir(document, {
depth: null
});