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.

CodePudding user response：

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;