Sum of column values inside Json array with group by

Question

I have next Django model.

class StocksHistory(models.Model):
    wh_data = models.JsonField()
    created_at = models.DateTimeField()

I store JSON data in wh_data.

[
   {
      "id":4124124,
      "stocks":[
         {
            "wh":507,
            "qty":2
         },
         {
            "wh":2737,
            "qty":1
         }
      ],
   },
   {
      "id":746457457,
      "stocks":[
         {
            "wh":507,
            "qty":3
         }
      ]
   }
]

Note: it's data for one row - 2022-06-06.

I need to calculate the sum inside stocks by grouping them by wh and by created_at so that the output is something like this

[
   {
      "wh":507,
      "qty":5,
      "created_at":"2022-06-06"
   },
   {
      "wh":2737,
      "qty":1,
      "created_at":"2022-06-06"
   },
      {
      "wh":507,
      "qty":0,
      "created_at":"2022-06-07"
   },
   {
      "wh":2737,
      "qty":2,
      "created_at":"2022-06-07"
   }
]

I know how to group by date, but I don't understand how to proceed with aggregations inside JsonField.

StocksHistory.objects.extra(select={'day': 'date( created_at )'})
.values('day')
.annotate(
    ???
)

A solution is suitable, both through Django ORM and through RAW SQL.

jian · Accepted Answer · 2022-06-30 09:15:53Z

1

demo

WITH cte AS (
    SELECT
        jsonb_path_query(js, '$[*].stocks.wh')::numeric AS wh,
        jsonb_path_query(js, '$[*].stocks.qty')::numeric AS b,
        _date
    FROM (
        VALUES ('[
   {
      "id":4124124,
      "stocks":[
         {
            "wh":507,
            "qty":2
         },
         {
            "wh":2737,
            "qty":1
         }
      ]
   },
   {
      "id":746457457,
      "stocks":[
         {
            "wh":507,
            "qty":3
         }
      ]
   }
]'::jsonb)) v (js),
        (
            VALUES ('2022-06-06'), ('2022-06-07')) ss_ (_date)
),
cte2 AS (
    SELECT
        wh, sum(b) AS qty,
        _date
    FROM
        cte
    GROUP BY
        1,
        3
    ORDER BY
        1
)
SELECT
    array_agg(row_to_json(cte2.*)::jsonb)
FROM
    cte2;

edited Jun 30, 2022 at 9:15

answered Jun 30, 2022 at 4:20

jian

5,0231 gold badge29 silver badges47 bronze badges

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

7 Comments

unknown Over a year ago

Thank you. Tell me, how can I get rid of the hardcode inside VALUES?

unknown Over a year ago

Dates can't be hardcoded. They need to be obtained from the created_at columns. If some date is missing, output 0.

unknown Over a year ago

Am I correct in understanding that you refer the first array object to 2022-06-06 and the second to 2022-06-07? This is not entirely correct, because I just gave an example of objects array that applies to 2022-06-06.

jian Over a year ago

@unknown You need to supply date information to the query.VALUES is one way to construct the date values. another way to use select to supply the date values.

jian Over a year ago

@unknown I added a new demo link. Hope that will help you. \

|

Collectives™ on Stack Overflow

Sum of column values inside Json array with group by

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related