CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkpymongo
kazantzakis
kazantzakis

Reputation: 73

How to group documents by a specific field in MongoDB

Currently, I have a collection book_orders that looks like this:

{
    "order_id" : 1,
    "customer_name": "Nikos",
    "order_type": "digital",
    "title": "Zorba",
    "price": 25
}
{
    "order_id" : 2,
    "customer_id": "Giorgos",
    "order_type": "physical_delivery",
    "title": "War and Peace",
    "price": 30
}
{
    "order_id" : 2,
    "customer_id": "Giorgos",
    "order_type": "digital",
    "title": "Children of the Gabalawi Street",
    "price": 35
}
{
    "order_id" : 3,
    "customer_id": "Giorgos",
    "order_type": "digital",
    "title": "The Desert of the Tartars",
    "price": 40
}

What I want to do with this collection is that I want to have an output like this, where I group orders by order_id, and embed them into lists named digital_orders and physical_orders by subsequent grouping by order_type.

I assume I would need to use aggregation but I am not sure how to go about this. It is essential that I can preserve fields like customer_name, title, and price. Any help appreciated.

{
  "order_id": 1,
  "customer_name": "Nikos",
  "digital_orders": [
    {
      "title": "Zorba",
      "price": 25
    }
  ],
  "physical_orders": []
},
{
  "order_id": 2,
  "customer_name": "Giorgos",
  "digital_orders": [
    {
      "title": "War and Peace",
      "total": 25
    }
  ],
  "physical_orders": [
    {
      "title": "Children of the Gabalawi Street",
      "price": 35
    }
  ]
},
{
  "order_id": 3,
  "customer_name": "Giorgos",
  "digital_orders": [],
  "physical_orders": [
    {
      "title": "The Desert of the Tartars",
      "total": 40
    }
  ]
}

Upvotes: 1

Views: 947

Answers (1)

turivishal
turivishal

Reputation: 36104

You can try,

  • $group by order_id and get first customer_name and get all orders with required fields in a array
  • $project to show required fields, make 2 fields one digital_orders to filter digital orders from orders array and second physical_orders to filter physical orders from orders array
db.collection.aggregate([
  {
    $group: {
      _id: "$order_id",
      customer_name: { $first: "$customer_name" },
      orders: {
        $push: {
          title: "$title",
          price: "$price",
          order_type: "$order_type"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      order_id: "$_id",
      customer_name: 1,
      digital_orders: {
        $filter: {
          input: "$orders",
          cond: { $eq: ["$$this.order_type", "digital"] }
        }
      },
      physical_orders: {
        $filter: {
          input: "$orders",
          cond: { $eq: ["$$this.order_type", "physical_delivery"] }
        }
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions