mysql - 检索值并从嵌套 Json 格式的表库中对它们求和 - SQL

Question

您好我正在尝试从表的 Nest Json 列中检索值并将它们求和。如下所示，

ID      Details    Created_At
2345               2021-05-31
3243               2021-06-02
3243               2021-06-02
3243               2021-06-02

这里的“详细信息”列是每行包含 json 值的列，格式如下

{
    "new_products": [
        {
            "bucket": "READY",
            "sku": "46123445323423",
            "overaged": 0,
            "transfer": 0,
            "total_count": 14,
            "actual_count": 14,
            "lock_quantity": 0
        },
     ],
    "old_products": [
        {
            "bucket": "READY",
            "sku": "451902224524",
            "overaged": 0,
            "transfer": 0,
            "total_count": 59,
            "actual_count": 59,
            "lock_quantity": 0
        },
        {
            "bucket": "READY",
            "sku": "46123445323423",
            "overaged": 0,
            "transfer": 0,
            "total_count": 14,
            "actual_count": 14,
            "lock_quantity": 0
        },
        {
            "bucket": "READY",
            "sku": "42342361484",
            "overaged": 0,
            "transfer": 0,
            "total_count": 29,
            "actual_count": 29,
            "lock_quantity": 0
        },
        {
            "bucket": "READY",
            "sku": "23234234342",
            "overaged": 0,
            "transfer": 0,
            "total_count": 57,
            "actual_count": 57,
            "lock_quantity": 0
        }
    ]
}

我在查询中能够实现的目标：

SELECT ID,
            Created_At,
          (json_extract(Details,'$.old_products') )  AS column
   FROM Table)

这只是在新列中提取“old_products”值，

但是，我试图将每行的键 'old_products','new_products' 的 total_count 、 actual_count 的所有值相加到一个新列中。

怎样才能做到这一点？

标签： mysqlsqlamazon-athenapresto