Given the following data structure:

[
  {
    "body": {
      "Fields": [
        {
          "Name": "description",
          "Value": "Some text"
        },
        {
          "Name": "size",
          "Value": "40"
        }
      ]
    }
  }
]

I need to get the following output containing keys extracted from 'Name' fields and values extracted by "Value" fields:

[
  {
    "description": "Some text",
    "size": "40"
  }
]

Could you please provide me with the ideas? I've ended up filtering required element, but have no idea how to extract values and assign them to the keys. What I have so far:

db.collection.aggregate([
  {
    "$project": {
      "description": {
        "$filter": {
          "input": "$body.Fields",
          "as": "bfields",
          "cond": {
            "$eq": [
              "$$bfields.Name",
              "description"
            ]
          }
        }
      },
      "size": {
        "$filter": {
          "input": "$body.Fields",
          "as": "bfields",
          "cond": {
            "$eq": [
              "$$bfields.Name",
              "size"
            ]
          }
        }
      }
    }
  }
])

It produces:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "description": [
      {
        "Name": "description",
        "Value": "Some text"
      }
    ],
    "size": [
      {
        "Name": "size",
        "Value": "40"
      }
    ]
  }
]

CodePudding user response：

 db.collection.aggregate([
 {
  $addFields: {
  body: {
    $map: {
      input: "$body.Fields",
      as: "fi",
      in: {
        k: "$$fi.Name",
        v: "$$fi.Value"
      }
    }
  }
}
},
{
 "$addFields": {
  "body": {
    "$arrayToObject": "$body"
   }
  }
 },
 {
  $project: {
  description: "$body.description",
  size: "$body.size",
  _id: 0
  }
 }
])

explained:

1. Use $map to rename the keys to k,v suitable for $arrayToObject
2. Convert the array to object with $arrayToObject ( the magic trick)
3. $project to the exact desired output

playground