列值＆＃x200B;内部JSON数组的总和与Group By

Question

我有下一个Django模型。

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

我将JSON数据存储在wh_data中。

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

-2022-06-06。

注意：它是一行的数据 create_at使输出是这样的，

[
   {
      "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"
   }
]

我知道如何按日期进行组，但是我不明白如何在jsonfield 。

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

解决方案都是合适的，无论是通过django orm还是通过RAW SQL。

Answer 1

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;