时间戳

首页 > 解决方案 > MySQL JSONField 嵌套数组 SUM

mysql - MySQL JSONField 嵌套数组 SUM

问题描述

我在 MySQL 8.0 数据库中有一个简单的表，如下所示：

+-----------+---------+----------+
| id        | data    |created   |
+-----------+---------+----------+
| INT       | JSON    |Timestamp |
+-----------+---------+----------+

我可以使用以下方法填充我的 JSONField：

INSERT INTO mytable (`data`) VALUES ('{
    "File": {
        "Files": {
            "Accounts": {
                "Account": [{
                    "AccountID": "11",
                    "AccountDescription": "CASH",
                    "Balance": "600.00"
                }, {
                    "AccountID": "111",
                    "AccountDescription": "Cash",
                    "Balance": "600.00"
                }]
            }
        }
    }
}');

我想要的是提取SUM所有 Balance 值。

我试过这个：

SELECT SUM(JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.File.Files.Accounts.Account[*].Balance'))) as 'result' FROM mytable WHERE id = 1;

但是给出了结果：

+-----------+
| result    |
+-----------+
| 0         |
+-----------+

另外，如果我摆脱SUM，结果是：

+-----------------------+
| result                |
+-----------------------+
| ["600.00", "600.00"]  |
+-----------------------+

这让我相信这JSON_UNQUOTE也不适用于这个嵌套数组 SELECT。

我如何查询表（最好没有自定义函数），以便它给出：

+-----------+
| result    |
+-----------+
| 1200      |
+-----------+

标签： mysqlmysql-8.0mysql-json

解决方案

为了得到想要的结果，可以JSON Table Functions在 MySQL 8.0 中使用。它将 JSON 数据转换为表格形式。然后您可以对结果使用聚合函数。

下面给出了实现相同的查询

SELECT sum(result) as result
  FROM mytable,
   JSON_TABLE(
    `data`,
     '$.File.Files.Accounts.Account[*]' COLUMNS(           
          NESTED PATH '$.Balance' COLUMNS (result DECIMAL PATH '$')           
    )
) AS jt;

DB小提琴链接如下
https://www.db-fiddle.com/f/vrn55vY2TMeVsVAzy9CS1w/11

有关 JSON 表函数的更多信息，请参见
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

推荐阅读