如何使用JSON函数从Mariadb中的JSON字段求和 [英] How to sum values from a json field in Mariadb using JSON functions

问题描述

在表格列中提供以下json字段:

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?

我能得到的最远的是

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

返回:

[590, 208]

我需要得到的是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

推荐答案

尝试:

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)

请参见 CONNECT JSON表类型.

更新

检查:

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)

这篇关于如何使用JSON函数从Mariadb中的JSON字段求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！