How to output/ map an array with only the required fields after a filter in mongodb?

Question

I am using this data:

{
"world": "Comic",
"characters": [
  {
    "character": "megaman",
    "type":"hero",
    "code":"123"
  },
  {
    "character": "dr willow",
    "type":"villain",
    "code":"1234"
  },
  {
    "character": "spiderman",
    "type":"hero",
    "code":"12345"
  },
  {
    "character": "venom",
    "type":"villain",
    "code":"123456"
  }
  
]
}

With this code:

    db.collection.aggregate([
        {$addFields:{
            array_hero:{
                $filter:{
                    input: "$characters",
                    cond: {$eq:["$$this.type","hero"]}
                }
            },
            array_villain:{
                $filter:{
                    input: "$characters",
                    cond: {$eq:["$$this.type","villain"]}
                }
            },
        }}
    ])

the output is:

    {
    "array_hero": [
        {
        "character": "megaman",
        "type": "hero",
        "code": "123"
        },
        {
        "character": "spiderman",
        "type": "hero",
        "code": "12345"
        }
    ],
    "array_villain": [
        {
        "character": "dr willow",
        "type": "villain",
        "code": "1234"
        },
        {
        "character": "venom",
        "type": "villain",
        "code": "123456"
        }
    ]
    }

I want to have this output, where the arrays are only built with the characters.codelike this:

   {
    "array_hero": [
       "123","12345"
    ],
    "array_villain": [
       "1234","123456"
    ]
   }

how can I do it?

The answers to this question should have what you're looking for: https://stackoverflow.com/questions/25589113/how-to-select-a-single-field-for-all-documents-in-a-mongodb-collection — SevvyP, Jul 16 '20 at 16:24
@SevvyP excuse my ignorance, in this case I use `aggregations` so it is something complex for me, I don't know how to do what I need. — yavg, Jul 16 '20 at 16:27
@yavg Added a correction. Please see if this [play link](https://mongoplayground.net/p/3aYqq-gElme) works for you. If yes, I ll post it as an answer then. — ambianBeing, Jul 16 '20 at 16:35

score 1 · Answer 1 · answered Jul 16 '20 at 16:58

Say we have saved following document into a collection named collection.

db.collection.save(
  {
    "world": "Comic",
    "characters": [
      {
        "character": "megaman",
        "type":"hero",
        "code":"123"
      },
      {
        "character": "dr willow",
        "type":"villain",
        "code":"1234"
      },
      {
        "character": "spiderman",
        "type":"hero",
        "code":"12345"
      },
      {
        "character": "venom",
        "type":"villain",
        "code":"123456"
      }
      
    ]
  }
)

Then the following command would return codes grouped by different types of characters that the world field is Comic in this collection:

db.collection.aggregate([
  {
    $match: {
      world: "Comic"
    },
  },
  {
    $unwind: "$characters"
  },
  {
    $group: {
      _id: "$characters.type",
      codes: {$addToSet: "$characters.code"}
    }
  },
  {
    $project: {
      _id: 0,
      type: "$_id",
      codes: 1
    }
  }
])

The result running above command in mongo shell is as following:

{ "codes" : [ "12345", "123" ], "type" : "hero" }
{ "codes" : [ "123456", "1234" ], "type" : "villain" }

score 1 · Accepted Answer · answered Jul 16 '20 at 17:04

Your aggregation query is almost there. Just added a $map stage to pick the required fields/attributes.

db.collection.aggregate([
  {
    $addFields: {
      array_hero: {
        $filter: {
          input: "$characters",
          cond: {
            $eq: [
              "$$this.type",
              "hero"
            ]
          }
        }
      },
      array_villain: {
        $filter: {
          input: "$characters",
          cond: {
            $eq: [
              "$$this.type",
              "villain"
            ]
          }
        }
      },
      
    },
    
  },
  {
    $project: {
      array_hero: {
        $map: {
          input: "$array_hero",
          as: "hero",
          in: "$$hero.code"
        }
      },
      array_villain: {
        $map: {
          input: "$array_villain",
          as: "villain",
          in: "$$villain.code"
        }
      },
      
    }
  }
])

Play link

Thank you very much, a little question, what should I do if I only want the `code` and the` character` to be displayed? — yavg, Jul 16 '20 at 17:16
@yavg Can tweak the `$map` stage to get all required fields from the filtered arrays. See if [this](https://mongoplayground.net/p/yrHrTfNRNzC) helps. — ambianBeing, Jul 16 '20 at 17:21

How to output/ map an array with only the required fields after a filter in mongodb?

2 Answers2

Linked