multiple grouping of documents with nested array in mongodb

Question

I have a list of documents with these fields id:'int', category:'text', field:'text', and a list of items[{title, value}]

     documents= [
      { "id":"1",
        "category": "education",
        "field": "science",
        "items": [
          {
            "title": "item 1",
            "value": "10"
          },
          {
            "title": "item 2",
            "value": "102"
          },
          {
            "title": "item 4",
            "value": "12"
          }
        ]
      },
      { "id":"2",
        "category": "education",
        "field": "science",
        "items": [
          {
            "title": "item 1",
            "value": "4"
          },
          {
            "title": "item 2",
            "value": "23"
          },
          {
            "title": "item 4",
            "value": "45"
          }
        ]
      },
      { "id":"3",
        "category": "fitness",
        "field": "body",
        "items": [
          {
            "title": "item 1",
            "value": "87"
          },
          {
            "title": "item 5",
            "value": "45"
          },
          {
            "title": "item =3",
            "value": "23"
          }
        ]
      },
 { "id":"4",
    "category": "education",
    "field": "culture",
    "items": [
      {
        "title": "item 1",
        "value": "187"
      },
      {
        "title": "item 5",
        "value": "145"
      },
      {
        "title": "item 3",
        "value": "123"
      }
    ]]

i'm working with mongodb (beginner) and i'm confused about how to group those documents firstly by category, then by field, then by item's title to push their value in array like a list of values history. the desired result:

    newDocument=[
    { "newid":"1",
      "category": "education",
      "field": "science",
      "items": [
        {
          "title": "item 1",
          "value": ["10","4"]
        },
        {
          "title": "item 2",
          "value": ["102","23"]
        },
        {
          "title": "item 4",
          "value": ["12", "45"]
        }
      ]
    },
{ "newid":"2",
        "category": "education",
        "field": "culture",
        "items": [
            {
              "title": "item 1",
              "value": ["187"]
            },
            {
              "title": "item 5",
              "value":["145"]
            },
            {
              "title": "item 3",
              "value": ["123"]
            }
          ]
      }
    { "newid":"3",
        "category": "fitness",
        "field": "body",
        "items": [
            {
              "title": "item 1",
              "value": ["87"]
            },
            {
              "title": "item 5",
              "value":["45"]
            },
            {
              "title": "item 3",
              "value": ["23"]
            }
          ]
      }
  ]

turivishal · Accepted Answer · 2022-01-22 15:02:00Z

1

You can use an aggregation query, but it will impact on performance and speed of response, there are two options, one using $unwind and another using $accumulator operator,

using `$accumulator`:

$group by category and field
$accumulator to do custom JavaScript logic and merge items array, you can improve and update as per your requirement
$project to show required fields

db.collection.aggregate([
  {
    $group: {
      _id: {
        category: "$category",
        field: "$field"
      },
      items: {
        $accumulator: {
          init: function() { return []; },
          accumulate: function(items1, items2) {
            return items1.concat(items2);
          },
          accumulateArgs: ["$items"],
          merge: function(items1, items2) {
            return items1.concat(items2);
          },
          finalize: function(state) { 
            var items = {}, finalItems = [];
            state.forEach(function(item) {
              if (items[item.title]) {
                items[item.title].value.push(item.value); 
              }
              else {
                item.value = [item.value];
                items[item.title] = item;
              }
            });
            for (var i in items) {
              finalItems.push(items[i]);
            }
            return finalItems; 
          },
          lang: "js"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      category: "$_id.category",
      field: "$_id.field",
      items: 1
    }
  }
])

using `$unwind`: (not recommended)

$unwind deconstruct the items array
$group by category, field and item title and construct the array if item values
$group by category and field and construct the items array with title and value
$project to show required fields

db.collection.aggregate([
  { $unwind: "$items" },
  {
    $group: {
      _id: {
        category: "$category",
        field: "$field",
        title: "$items.title"
      },
      value: { $push: "$items.value" }
    }
  },
  {
    $group: {
      _id: {
        category: "$_id.category",
        field: "$_id.field"
      },
      items: {
        $push: {
          title: "$_id.title",
          value: "$value"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      category: "$_id.category",
      field: "$_id.field",
      items: 1
    }
  }
])

Playground

edited Jan 22, 2022 at 15:02

answered Jan 22, 2022 at 12:34

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ayoub Benayache Over a year ago

thank you, it's working, is there any way to remove unwind, as you said, this will affect the performance, which is my important parameter

turivishal Over a year ago

I have updated the answer with another option, without $unwind, as per your knowledge $unwind does not impact on performance, but after that regroup $group impact on performance. so multiple $group stage is not a good way.

Collectives™ on Stack Overflow

multiple grouping of documents with nested array in mongodb

1 Answer 1

using `$accumulator`:

using `$unwind`: (not recommended)

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

using $accumulator:

using $unwind: (not recommended)

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

using `$accumulator`:

using `$unwind`: (not recommended)