How to sum values from a json field in Mariadb using JSON functions

Question

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

What version of MariaDB are you using?.

wchiquito
– wchiquito

2017-07-09 14:51:14 +00:00
Commented Jul 9, 2017 at 14:51 — wchiquito
– wchiquito, Commented Jul 9, 2017 at 14:51
10.2.6-MariaDB-10.2.6+maria~jessie-log

spacebiker
– spacebiker

2017-07-09 17:40:23 +00:00
Commented Jul 9, 2017 at 17:40 — spacebiker
– spacebiker, Commented Jul 9, 2017 at 17:40

wchiquito · Accepted Answer · 2017-07-09 17:58:55Z

2

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)

edited Jul 9, 2017 at 17:58

answered Jul 9, 2017 at 17:10

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

spacebiker Over a year ago

Thanks for the answer. But I get the following error: #1305 - FUNCTION tbl.JsonGet_Int does not exist

spacebiker Over a year ago

plugin_dir: /usr/lib/mysql/plugin/, and ls /usr/lib/mysql/plugin/ lists ha_connect.so correctly.

spacebiker Over a year ago

After running the last line CREATE FUNCTION IF NOT EXISTS jsonget_int RETURNS integer SONAME 'ha_connect.so'; it started to work.. Nice one!

dovregubben Over a year ago

I'm facing the same challenge. My JSON data is looking quite similar to yours and I also need to sum up certain values, but these are decimals. So I tried to create that function: CREATE FUNCTION IF NOT EXISTS jsonget_int RETURNS DECIMAL SONAME 'ha_connect.so'; but querying that function will crash my database service, e.g. SELECT JsonGet_Int( JSON_EXTRACT(@json, '$.trades[*].trade.price'),'[+]') SUM; Can you help me adapt your solution to sum decimals as well? I'm using MariaDB 10.3.31

wchiquito Over a year ago

@dovregubben: Try JsonGet_Real.

Collectives™ on Stack Overflow

How to sum values from a json field in Mariadb using JSON functions

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related