如何使用 JSON 函数对 Mariadb 中 json 字段的值求和

在表格列中给出以下 json 字段:

<代码>[{"付款日期":"2016-04-26",金额":590，付款方式":2"},{"付款日期":"2017-05-01",金额":208，付款方式":4"}]

我怎样才能把所有的金额加起来?

我能走的最远是

SELECT JSON_EXTRACT(`payment_lines`, '$[*].amount') FROM tbl

返回:

[590, 208]

我需要得到的是 590 和 208 的总和.也就是说，在这种情况下有两行，但可能还有更多.

选择版本()；10.2.6-MariaDB-10.2.6+maria~jessie-log

解决方案

试试:

MariaDB [(none)]>选择版本()；+------------------------+|版本() |+------------------------+|10.2.6-MariaDB-10.2.6 |+------------------------+一组中的 1 行(0.00 秒)MariaDB [(无)]>SET @`JSON` := ''>['>{'>"付款日期":"2016-04-26",'>金额":590，'>付款方式":2"'>},'>{'>"付款日期":"2017-05-01",'>金额":208，'>付款方式":4"'>}'>]';查询正常，0 行受影响(0.00 秒)MariaDB [(无)]>选择->JsonGet_Int(->JSON_EXTRACT(@`JSON`, '$[*].amount'),->'[+]'->) `总和`;+------+|总和 |+------+|798 |+------+一组中的 1 行(0.00 秒)

请参阅 CONNECT JSON 表类型..p>

更新

检查:

MariaDB [(none)]>在 `Variable_name` = 'plugin_dir' 处显示变量；+---------------+-------------+|变量名 |价值 |+---------------+-------------+|插件目录 |/usr/lib/mysql/plugin/|+---------------+-------------+一组中的 1 行(0.00 秒)MariaDB [(无)]>！ls -1/usr/lib/mysql/plugin/|grep 'ha_connect'ha_connect.soMariaDB [(无)]>如果不存在则创建函数 jsonget_int 返回整数->SONAME 'ha_connect.so';查询正常，0 行受影响(0.00 秒)

Giving the following json field in a table column:

[
  {
    "payment_date":"2016-04-26",
    "amount":590,
    "payment_method":"2"
  },
  {
    "payment_date":"2017-05-01",
    "amount":208,
    "payment_method":"4"
  }
]

How could I sum all the amounts?

The farthest I could get is

SELECT JSON_EXTRACT(`payment_lines`, '$[*].amount') FROM tbl

which returns:

[590, 208]

What I would need to get is this sum of 590 and 208. So to say that in this case there are two rows but there might be many more.

SELECT VERSION();
10.2.6-MariaDB-10.2.6+maria~jessie-log

解决方案

Try:

MariaDB [(none)]> SELECT VERSION();
+-----------------------+
| VERSION()             |
+-----------------------+
| 10.2.6-MariaDB-10.2.6 |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET @`JSON` := '
    '> [
    '>   {
    '>     "payment_date":"2016-04-26",
    '>     "amount":590,
    '>     "payment_method":"2"
    '>   },
    '>   {
    '>     "payment_date":"2017-05-01",
    '>     "amount":208,
    '>     "payment_method":"4"
    '>   }
    '> ]';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT
    ->   JsonGet_Int(
    ->     JSON_EXTRACT(@`JSON`, '$[*].amount'),
    ->     '[+]'
    ->   ) `SUM`;
+------+
| SUM  |
+------+
|  798 |
+------+
1 row in set (0.00 sec)

See CONNECT JSON Table Type.

UPDATE

Check:

MariaDB [(none)]> SHOW VARIABLES WHERE `Variable_name` = 'plugin_dir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> ! ls -1 /usr/lib/mysql/plugin/ | grep 'ha_connect'
ha_connect.so

MariaDB [(none)]> CREATE FUNCTION IF NOT EXISTS jsonget_int RETURNS integer
    -> SONAME 'ha_connect.so';
Query OK, 0 rows affected (0.00 sec)