Multiple Grouping in NoSQL

Question

I'm developing a Billing Software for maintaining daily records. I'm using PyGTk(for UI) and MongoDB(NoSQL) database. I'm storing record of each bill in following format

{
    "_id" : ObjectId("5b83f95a3859201d46385779"),
    "date" : "27/08/2018",
    "bill_number" : 7,
    "bill_amount" : 85,
    "payment_mode" : "Cash",
    "bill_content" : [
        {
            "item_code" : 1,
            "item_name" : "abc",
            "item_count" : 1
        },
        {
            "item_code" : 2,
            "item_name" : "xyz",
            "item_count" : 2
        }
    ]
}
{
    "_id" : ObjectId("5b83f95f3859201d4638577b"),
    "date" : "27/08/2018",
    "bill_number" : 8,
    "bill_amount" : 125,
    "payment_mode" : "Card",
    "bill_content" : [
        {
            "item_code" : 3,
            "item_name" : "xyz",
            "item_count" : 2
        },
        {
            "item_code" : 2,
            "item_name" : "abc",
            "item_count" : 1
        }
    ]
}
{
    "_id" : ObjectId("5b83f95f3859201d4638577b"),
    "date" : "27/08/2018",
    "bill_number" : 8,
    "bill_amount" : 125,
    "payment_mode" : "Online",
    "bill_content" : [
        {
            "item_code" : 3,
            "item_name" : "abc",
            "item_count" : 1
        },
        {
            "item_code" : 2,
            "item_name" : "xyz",
            "item_count" : 3
        },
        {
            "item_code" : 1,
            "item_name" : "pqr",
            "item_count" : 1
        }
    ]
}

I want the following things from the records:

Daily Total Amount
Sum of amount grouped by payment mode
Count of each Item on daily basis(if possible)

Sample output

{
'date':'27/08/2018',
'total_amount': 335,
'payment_mode': [{'Cash': 85},{'Card': 125}, 'Online': 125],
'item_count': [{'abc':3},{'xyz':5},{'pqr':1}]
}

I'm new to NoSQL hence can't figure out to do all the things in one query. Please tell me if it's possible or not or if there's any better way to store the records.

I'm relatively new to NoSQL as well, but I think you may have to start working with some secondary indexes. NoSQL is not made to treat data like a relational database would. NoSQL data is modeled more by considering the questions you want to ask, not how to store the data. Maybe start here? https://highlyscalable.wordpress.com/2012/03/01/nosql-data-modeling-techniques/ — Matt Runion, Aug 27 '18 at 14:03
thanks @mrunion for the link. I was relating NoSQL to relational database while modeling. I'll read and understand the concepts. — Abhishek, Aug 28 '18 at 10:13

score 0 · Answer 1 · answered Aug 27 '18 at 16:59

If you want a specific attribute from each record, then you can easily access it by creating a cursor and iterating every record in it.

suppose your collection name is 'billing_software', then you can use the following snippet to get your desired output:

collection = db.billing.software
all_records = collection.find({})

for records in all_records:
    temp = dict()
    temp['date'] = records['date]
    print(temp)

Anyhow, coming back to your original question. In your case, you might want to aggregate and then group the collection by date.

Helpful links: 1. https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/ 2. https://docs.mongodb.com/manual/reference/operator/aggregation/group/

Multiple Grouping in NoSQL

1 Answers1