The data for a collection (named product_category) in the MongoDB database is as follows:

[
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 1234,
    "category": "drink"
  },
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 2122,
    "category": "soda"
  },
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 2121,
    "category": "mocktail"
  },
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 2121,
    "category": "mocktail"
  }
]

Requirement: Find non duplicate categories for the product and create a multi-layered structure, where two product fields are located in the upper layer and two category fields are located in the lower layer array. The expected results are as follows:

[
  {
    "name": "sprit",
    "product_id": 1212,
    "categogies": [
      {
        "category": "soda",
        "category_id": 2122
      },
      {
        "category": "drink",
        "category_id": 1234
      },
      {
        "category": "mocktail",
        "category_id": 2121
      }
    ]
  }
]

The syntax of MongoDB query itself is quite long, and simple grouping requires writing many lines:

db.product_category.aggregate([
  {
    $group: {
      _id: "$product_id",
      name: {
        $first: "$name"
      },
      categogies: {
        $addToSet: {
          "category_id": "$category_id",
          "category": "$category"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      product_id: "$_id",
      name: "$name",
      categogies: "$categogies"
    }
  }
])

SPL grouping can retain subsets of grouping, which can implement it in just one line and is easy to read and understand:

	A
1	=mongo_open@d("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'product_category','projection':{'_id':0}}")
3	=mongo_close(A1)
4	=A2.group(product_id;~.name,~.groups(category_id;category):categories)
5	=json(A4)

The first three lines are readings, the fourth line performs calculation, and the fifth line converts the result back to JSON format.

Question source: https://stackoverflow.com/questions/78447108/mongodb-convert-response-into-array-of-object-in-query