###############################################################################

      *****************        Hey! Welcome to the Kwoosh Workshop
     *******************       -----------------------------------
    *******  *****  *****
   ********  ****  *******     This is where we talk about the things
  *********  ***  *********    we're working on.
 **********  **  ***********
***********     *************  Most things start as messy ideas before
 **********  **  ***********   they get polished into finished features.
  *********  ***  *********
   ********  ****  *******     This is a place for messy ideas.
    *******  *****  *****
     *******************       For the completed product see kwoosh.com
      *****************

###############################################################################

Aggregating Nested Values with JSONFields

Integrating with 3rd party APIs you'll often need to implement a webhook for your application to respond to external events. It's useful to take the event data and store it in your Database for future reference.

As Kwoosh is run out of Texas, users in Texas need to pay an 8.25% sales tax. To my surprise Stripe's dashboard doesn't seem to offer an answer to a simple question: How much did I collect that was taxable and how much do I need to remit to the state for last quarter's sales.

Armed with our event log data and Python we can quickly get that the numbers we're looking for.

Our data looks something like this. Naturally a real event would have a lot more data, but for our purposes today this should suffice.

This data is sent with eachinvoice.payment_successfulevent and it's saved in aa JSONField in our database. Using the KeyTransform we can extract values from our JSONField and annotate them to our QuerySet. Even better, as of Django 1.11 we can nest our KeyTransform so we can extract values that are multiple levels deep.

Our plan of attack is to annotate the value, sum them together, and return them with the period. Unfortunately we have to sum them in Python until bug#29139is fixed. We're not summing millions of rows so it's still quick enough.

And just like that we can aggregate values from our JSONFields. If you want to sum other fields you can simplyadd another line like this, but replace "tax" with the field you want to sum.

To make it a bit more useful, I built a second function that uses this function to give me quarterly sums.

We can simply iterate over this function and generate a simple report that shows us a quarterly breakdown of our sales and taxes collected.

—jvd