Using replaceRoot in mongoDB

Question

I have my collection like this :

{
   {
      "productType":"Bike",
      "company":"yamaha",
      "model":"y1"
   },
   {
      "productType":"Bike",
      "company":"bajaj",
      "model":"b1"
   },
   {
      "productType":"Bike",
      "company":"yamaha",
      "model":"y1"
   },
   {
      "productType":"Car",
      "company":"Maruti",
      "model":"m1"
   },
   {
      "productType":"Bike",
      "company":"yamaha",
      "model":"y2"
   },
   {
      "productType":"Car",
      "company":"Suzuki",
      "model":"s1"
   }
}

I want this data :


  [ "Bike":{
      "Yamaha":{
         "y1":2,
         "y2":1
      },
      "Bajaj":{
         "b1":1
      }
   },
   "Car":{
      "Maruti":{
         "m1":1
      },
      "Suzuki":{
         "s1":1
      }
   
]

I tried to use $replaceRoot but could not do it with that (Key should be the productType then company then the model with count of that model as the value). How to do this ? In which case specifically we should use $replaceRoot ?

Edit : since the whole answer was inside a json object, I removed that.

turivishal · Accepted Answer · 2021-03-14 17:26:40Z

3

$group by productType, company, and model, and count the total
$group by productType and company, construct array using model and count of models in key-value format
$group by productType, construct array of company using company and model object that is converted from array using $arrayToObject
$group by null, construct array of product using product and company object that is converted from array using $arrayToObject
$replaceRoot to replace converted product array to object using $arrayToObject

db.collection.aggregate([
  {
    $group: {
      _id: {
        productType: "$productType",
        company: "$company",
        model: "$model"
      },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: {
        productType: "$_id.productType",
        company: "$_id.company"
      },
      model: {
        $push: {
          k: "$_id.model",
          v: "$count"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.productType",
      company: {
        $push: {
          k: "$_id.company",
          v: { $arrayToObject: "$model" }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      product: {
        $push: {
          k: "$_id",
          v: { $arrayToObject: "$company" }
        }
      }
    }
  },
  { $replaceRoot: { newRoot: { $arrayToObject: "$product" } } }
])

Playground

edited Mar 14, 2021 at 17:26

answered Mar 14, 2021 at 15:09

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

3 Comments

Anmol Jain Over a year ago

I am just getting one extra parent (or the key) in which whole answer is surrounded as the value. That key is named "products".

turivishal Over a year ago

can you confirm this mongoplayground.net/p/8__hXTjL2hf

turivishal Over a year ago

aggregate() always return in array, so you can use direct zero element from response.

Dheemanth Bhat · Accepted Answer · 2021-03-14 16:20:41Z

SOLUTION #1: Result as separate documents.

db.products.aggregate([
    {
        $group: {
            _id: {
                company: "$company",
                model: "$model"
            },
            productType: { $first: "$productType" },
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: "$_id.company",
            productType: { $first: "$productType" },
            v: {
                $push: {
                    k: "$_id.model",
                    v: "$count"
                }
            }
        }
    },
    {
        $group: {
            _id: "$productType",
            k: { $first: "$productType" },
            v: {
                $push: {
                    k: "$_id",
                    v: { $arrayToObject: "$v" }
                }
            }
        }
    },
    {
        $replaceWith: {
            array: [
                {
                    k: "$k",
                    v: { $arrayToObject: "$v" }
                }
            ]
        }
    },
    {
        $replaceRoot: {
            newRoot: { $arrayToObject: "$array" }
        }
    }
]);

Output: Result as separate documents.

/* 1 */
{
    "Bike" : {
        "yamaha" : {
            "y1" : 2,
            "y2" : 1
        },
        "bajaj" : {
            "b1" : 1
        }
    }
},

/* 2 */
{
    "Car" : {
        "Maruti" : {
            "m1" : 1
        },
        "Suzuki" : {
            "s1" : 1
        }
    }
}

SOLUTION #2: Result as single document.

db.products.aggregate([
    {
        $group: {
            _id: {
                company: "$company",
                model: "$model"
            },
            productType: { $first: "$productType" },
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: "$_id.company",
            productType: { $first: "$productType" },
            v: {
                $push: {
                    k: "$_id.model",
                    v: "$count"
                }
            }
        }
    },
    {
        $group: {
            _id: "$productType",
            k: { $first: "$productType" },
            v: {
                $push: {
                    k: "$_id",
                    v: { $arrayToObject: "$v" }
                }
            }
        }
    },
    {
        $group: {
            _id: null,
            array: {
                $push: {
                    k: "$k",
                    v: { $arrayToObject: "$v" }
                }
            }
        }
    },
    {
        $replaceRoot: {
            newRoot: { $arrayToObject: "$array" }
        }
    }
]);

Output: Result as single document.

{
    "Bike" : {
        "bajaj" : {
            "b1" : 1
        },
        "yamaha" : {
            "y1" : 2,
            "y2" : 1
        }
    },
    "Car" : {
        "Maruti" : {
            "m1" : 1
        },
        "Suzuki" : {
            "s1" : 1
        }
    }
}

Collectives™ on Stack Overflow

Using replaceRoot in mongoDB

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related