About merging/grouping documents with composite key in Mongodb 4.4

Question

i am new to mongodb, and this issue make me feel confuse and i have no idea how to do so. Heres 3 documents example in mongodb collection.

I tried multiple way but still not success, can any one help with following sample? Does it really possible to do so with mongodb aggregation pipeline? The key consider which should merge are machine number, model and license numer

[
    {
        "INFO": "A",
        "MACHINE": [
            {
                "NUMBER": "0001",
                "MACHINE_MODEL": "A001"
            },
            {
                "NUMBER": "0002",
                "MACHINE_MODEL": "A002"
            }
        ],
        "LICENSE": [
            {
                "NUMBER": "1111"
            },
            {
                "NUMBER": "2222"
            }
        ]
    },
    {
        "INFO": "B",
        "MACHINE": [
            {
                "NUMBER": "0001",
                "MACHINE_MODEL": "A001"
            }
        ],
        "LICENSE": [
            {
                "NUMBER": "1111"
            }
        ]
    },
    {
        "INFO": "C",
        "MACHINE": [
            {
                "NUMBER": "0002",
                "MACHINE_MODEL": "A002"
            }
        ],
        "LICENSE": [
            {
                "NUMBER": "2222"
            }
        ]
    },
    {
        "INFO": "D",
        "MACHINE": [
            {
                "NUMBER": "0001",
                "MACHINE_MODEL": "A001"
            },
            {
                "NUMBER": "0003",
                "MACHINE_MODEL": "A003"
            }
        ],
        "LICENSE": [
            {
                "NUMBER": "1111"
            },
            {
                "NUMBER": "3333"
            }
        ]
    },
    {
        "INFO": "GG",
        "MACHINE": [
            {
                "NUMBER": "9999",
                "MACHINE_MODEL": "A999"
            }
        ],
        "LICENSE": [
            {
                "NUMBER": "42069"
            }
        ]
    }
]

How can I merge/group this as below result with aggregation and output in new collection:

[
    {
        "INFO": [
            "A",
            "B",
            "C",
            "D"
        ],
        "MACHINE": [
            {
                "NUMBER": "0001",
                "MACHINE_MODEL": "A001"
            },
            {
                "NUMBER": "0002",
                "MACHINE_MODEL": "A002"
            },
            {
                "NUMBER": "0003",
                "MACHINE_MODEL": "A003"
            }
        ],
        "LICENSE": [
            {
                "NUMBER": "1111"
            },
            {
                "NUMBER": "2222"
            },
            {
                "NUMBER": "3333"
            }
        ]
    },
    {
        "INFO": "GG",
        "MACHINE": [
            {
                "NUMBER": "9999",
                "MACHINE_MODEL": "A999"
            }
        ],
        "LICENSE": [
            {
                "NUMBER": "42069"
            }
        ]
    }
]

R2D2 · Answer 1 · 2023-04-03T21:47:07.093

Not sure what is the expect difference in the result between "GG" and "A","B","C" & "D" , but if there is no difference , here is one option to group them removing the duplicates:

 db.collection.aggregate([
 {
  $unwind: "$MACHINE"
 },
 {
  $group: {
   _id: {
      NUMBER: "$MACHINE.NUMBER",
      MACHINE_MODEL: "$MACHINE.MACHINE_MODEL"
  },
  INFO: {
    "$addToSet": "$INFO"
  },
  LICENSE: {
    "$addToSet": "$LICENSE"
  },
  id: {
    $last: "$_id"
     }
   }
  },
 {
  $group: {
  _id: "$id",
  "MACHINE": {
    "$push": "$_id"
  },
  "LICENSE": {
    $last: "$LICENSE"
  },
  "INFO": {
     $last: "$INFO"
     }
  }
 },
 {
  "$addFields": {
   "LICENSE": {
    "$reduce": {
      "input": "$LICENSE",
      "initialValue": [],
      "in": {
        "$setUnion": [
          "$$this",
          "$$value"
           ]
         }
       }
      }
     }
    },
  {
   "$group": {
  "_id": "",
  "INFO": {
    $push: "$INFO"
  },
  "LICENSE": {
    $push: "$LICENSE"
  },
  "MACHINE": {
     $push: "$MACHINE"
    }
  }
 },
{
"$addFields": {
  "LICENSE": {
    "$reduce": {
      "input": "$LICENSE",
      "initialValue": [],
      "in": {
        "$setUnion": [
          "$$this",
          "$$value"
        ]
      }
    }
  },
  "INFO": {
    "$reduce": {
      "input": "$INFO",
      "initialValue": [],
      "in": {
        "$setUnion": [
          "$$this",
          "$$value"
        ]
      }
     }
    },
 "MACHINE": {
    "$reduce": {
      "input": "$MACHINE",
      "initialValue": [],
      "in": {
        "$setUnion": [
          "$$this",
          "$$value"
        ]
      }
     }
    }
   }
  }
])

Explained:

$unwind the MACHINE array of objects
group by key/values from MACHINE objects to get disticts
$reduce/$setUnion the LICENSE to get distints
$reduce/$setUnion the INFO to get distincts
You can add one last stage to output to a new collection: { $out:"theNewCollection" }

Playground

That's not I want, because "GG"has no common LICENSE & MACHINE with others — Edmund, Apr 03 '23 at 23:47
All i want is only merging those document who sharing same (but not exact match) MACHINE NUMBER, MACHINE MODEL and LICENSE NUMBER — Edmund, Apr 04 '23 at 00:20

About merging/grouping documents with composite key in Mongodb 4.4

1 Answers1