MongoDB Group aggregation multiple field can push specific array field?

user_9400974 0
Viewed 323

my mongodb document below

{
    "seq": 1,
    "cut": [
        {
            "script":[
                {
                    "type": "a",
                    "text": "a1"
                },
                {
                    "type": "a",
                    "text": "a2"
                },
                {
                    "type": "b",
                    "text": "b1"
                },
                {
                    "type": "c",
                    "text": "c1"
                }
            ]
        },
        {
            "script":[
                {
                    "type": "d",
                    "text": "d1"
                },
                {
                    "type": "a",
                    "text": "a3"
                },
                {
                    "type": "c",
                    "text": "c2"
                },
                {
                    "type": "b",
                    "text": "b2"
                }
            ]
        }
    ]
}

i want document aggregation with type key using $unwind and $group like below.

{
    "seq": 1,
    "a": ["a1", "a2", "a3"],
    "b": ["b1", "b2"],
    "c": ["c1", "c2"],
    "d": ["d1"]
}

first, use $unwind aggregation.

$unwind: {
    path: "$cut",
    preserveNullAndEmptyArrays: false
}

and, it first aggregaion pipeline result is:

{
    "seq": 1,
    "cut": {
        "script":[
            {
                "type": "a",
                "text": "a1"
            },
            {
                "type": "a",
                "text": "a2"
            },
            {
                "type": "b",
                "text": "b1"
            },
            {
                "type": "c",
                "text": "c1"
            }
        ]
        
    }
}

{
    "seq": 1,
    "cut": {
        "script":[
            {
                "type": "d",
                "text": "d1"
            },
            {
                "type": "a",
                "text": "a3"
            },
            {
                "type": "c",
                "text": "c2"
            },
            {
                "type": "b",
                "text": "b2"
            }
        ]
    }
}

next, unwind cut.script.

$unwind: {
  path: "$cut.script",
  preserveNullAndEmptyArrays: false
}

its result is:

{
    "seq": 1,
    "cut": {
        "script":{
            "type": "a",
            "text": "a1"
        }
    }
}

{
    "seq": 1,
    "cut": {
        "script":{
            "type": "a",
            "text": "a2"
        }
    }
}

and more 6 document..

and last, i want this results grouping with seq field. so i use $push expression.

{
    _id: "$seq",
    "a": {
        $push: "$cut.script.text"
    },
    "b": {
        $push: "$cut.script.text"
    },
    "c": {
        $push: "$cut.script.text"
    },
    "d": {
        $push: "$cut.script.text"
    }
}

but it $push expression is push all item of script. how can $push use condition pushes specific field (ex; "text" value with "type" equal to "a") value?

mongodb-compass aggregation-framework mongodb-query mongodb
3 Answers

You can try like this using $cond in aggregation

      {
        $group: {
          _id: "$seq",
          "a": {
            $push: {
              $cond: { 
                if: { $eq: [ "$cut.script.type", "a" ] }, 
                then: ["$cut.script.text"] ,
                else: null,
              }
            }
          }
        }
      }
user_79671190

I would prefer a more dynamic/generic solution:

db.collection.aggregate([
   { $unwind: "$cut" },
   { $unwind: "$cut.script" },
   {
      $group: {
         _id: { seq: "$seq", type: "$cut.script.type" },
         text: { $push: "$cut.script.text" }
      }
   },
   { $sort: { "_id.type": 1 } },
   {
      $group: {
         _id: "$_id.seq",
         data: { $push: { k: "$_id.type", v: "$text" } }
      }
   },
   { $replaceWith: { $mergeObjects: [{ seq: "$_id" }, { $arrayToObject: "$data" }] } }
])

Mongo Playground

user_30272660

Try this (Mongoplayground Link):

db.collection.aggregate({
  $unwind: {
    path: "$cut",
    preserveNullAndEmptyArrays: false
  }
},
{
  $unwind: {
    path: "$cut.script"
  }
},
{
  $group: {
    "_id": "$seq",
    "a": {
      $push: {
        $cond: {
          if: {
            $eq: [
              "$cut.script.type",
              "a"
            ]
          },
          then: "$cut.script.text",
          else: "$$REMOVE"
        }
      }
    },
    "b": {
      $push: {
        $cond: {
          if: {
            $eq: [
              "$cut.script.type",
              "b"
            ]
          },
          then: "$cut.script.text",
          else: "$$REMOVE"
        }
      }
    },
    "c": {
      $push: {
        $cond: {
          if: {
            $eq: [
              "$cut.script.type",
              "c"
            ]
          },
          then: "$cut.script.text",
          else: "$$REMOVE"
        }
      }
    },
    "d": {
      $push: {
        $cond: {
          if: {
            $eq: [
              "$cut.script.type",
              "d"
            ]
          },
          then: "$cut.script.text",
          else: "$$REMOVE"
        }
      }
    }
  }
})
user_67183530
Related