Aggregation, returning documents with objects, glued back from two sections: translated to default language and language-inspecific

Working with Data
1

Dear Community,

Looking for help with non-trivial aggregation query. Given: a collection with multiple documents. All of them have some special structure, containing fixed fields, unknown dynamic fields and translations. I want to build a view, which will return fixed fields (present in each document) and all other objects under “CustomDefinitionItems”. The problem is: content of these dynamically defined objects is split between two document sections: default (“none”) and translated one, e.g. “en”. I stuck on the stage, when I filtered out fixed field contents and have an array of custom definition items, which contains pieces of objects, which should be merged together. $ArrayToObject does not work, as

If the name of a field repeats in the array,

  • Starting in 4.0.5, $arrayToObject uses the last value for that field. For 4.0.0-4.0.4, the value used depends on the driver.*

I tried to use $mergeObjects, $group, $reduce without success.

Input (more in the playground here)
{
“_id”: {
“$oid”: “639b17befd601480ffbb06d6”
},
“payload”: {
“data”: {
“none”: {
“GENERAL”: {
“uom”: “l”,
“tuf”: “7000”,
“mod”: {
“type”: “FORMULA”,
“start”: “2019-01-01T01:00:00Z”,
“t”: “1y”,
“f”: “1”
},
“class”: “A013”,
“jurisdiction”: “GB”,
“language”: “EN”,
“Issuer”: {
“CompanyAddress”: {
“Country”: “GB”
}
},
“Redemption”: {
“MinimumQuantity”: {
“UomRedemption”: “l”,
“Amount”: “25”
},
“ValidFrom”: “1496271600”,
“ValidUntil”: “1559343600”
}
},
“GOODS”: {
“Chemistry”: {
“Purity”: “99.3”,
“Analysis”: {
“MeltingPoint”: “-114”,
“RefractiveIndexWaveLength”: “0.5876”,
“RefractiveIndex”: “1.3616”
}
}
}
},
“en”: {
“GENERAL”: {
“name”: “Ethanol”,
“description”: “Technical Ethanol, made from Wheat. Can be used for various purposes. For example to make fake Vodka, or to clean the engine.”,
“Issuer”: {
“CompanyName”: “Dry Spirits Inc.”,
“ContactPerson”: “jonas.drinkfest@dryspirits.com”,
“CompanyAddress”: {
“Addressline1”: “Jamestown road 22”,
“Addressline2”: “34A/5”,
“ZIP”: “A839I2”,
“Town”: “Herefordshire”
},
“WirePhone”: “0044094358309458”,
“MobilePhone”: “004478787283472”,
“Website”: “https://dryspirits.uk”,
“OfficePhone”: “00449837598473”,
“Fax”: “004419823791827”,
“EmailAddress”: “office@dryspirits.uk”,
“Timezone”: “GMT”
},
“Redemption”: {
“Condition”: “Shipping with our transportation partner Koala Limps Ltd.”,
“Address”: {
“Incoterms”: “EXW”
}
}
},
“GOODS”: {
“Type”: “Industrial Precursors”,
“Chemistry”: {
“Formula”: “C2H5OH”,
“Quality”: “PA”
}
}
}
},
“defaultlanguage”: “en”
}
}

Already created aggregate (1. It selects the part of the payload, from the language section, which is same as payload.defaultlanguage. 2. It processes the GENERAL section and selects only objects, which are not in the fixed field list)
db.collection.aggregate([
{
$addFields: {
defLangData: {
$arrayElemAt: [
{
$map: {
input: {
$filter: {
input: {
$objectToArray: “$payload.data”
},
as: “arr”,
cond: {
$eq: [
“$$arr.k”,
“$payload.defaultlanguage”
]
}
}
},
as: “arr”,
in: “$$arr.v”
}
},
0
]
}
}
},
{
$addFields: {
CustomDefinitionItems: {
$filter: {
input: {
$concatArrays: [
{
$objectToArray: “$defLangData.GENERAL”
},
{
$objectToArray: “$payload.data.none.GENERAL”
}
]
},
as: “arr”,
cond: {
$not: {
$in: [
“$$arr.k”,
[
“name”,
“description”,
“language”,
“jurisdiction”,
“uom”,
“currency”,
“tuf”,
“mod”,
“class”,
“refId”
]
]
}
}
}
}
}
}
])

Result can be observed in the playground.

Result, to achieve for the given input in the CustomDefinitionItems:
“CustomDefinitionItems”:
{
“GENERAL”:{
“Issuer”: {
“CompanyName”: “Dry Spirits Inc.”,
“ContactPerson”: “jonas.drinkfest@dryspirits.com”,
“CompanyAddress”: {
“Addressline1”: “Jamestown road 22”,
“Addressline2”: “34A/5”,
“ZIP”: “A839I2”,
“Town”: “Herefordshire”,
“Country”: “GB”
},
“WirePhone”: “0044094358309458”,
“MobilePhone”: “004478787283472”,
“Website”: “https://dryspirits.uk”,
“OfficePhone”: “00449837598473”,
“Fax”: “004419823791827”,
“EmailAddress”: “office@dryspirits.uk”,
“Timezone”: “GMT”
},
“Redemption”: {
“Condition”: “Shipping with our transportation partner Koala Limps Ltd.”,
“Address”: {
“Incoterms”: “EXW”
},
“MinimumQuantity”: {
“UomRedemption”: “l”,
“Amount”: “25”
},
“ValidFrom”: “1496271600”,
“ValidUntil”: “1559343600”
}
},
“GOODS”:{
“Type”: “Industrial Precursors”,
“Chemistry”: {
“Purity”: “99.3”,
“Formula”: “C2H5OH”,
“Quality”: “PA”,
“Analysis”: {
“MeltingPoint”: “-114”,
“RefractiveIndexWaveLength”: “0.5876”,
“RefractiveIndex”: “1.3616”
}
}
}
So with other words: Documents in db contain some objects with known structure and objects with unknown structure, split between translatable and non-translatable sections. In the view these objects should be returned as whole (somehow recursively glued back). Please, help, fighting with this for quite time already.

Best Regards,
Jevgeny