MySQL JSONField Nested Array SUM

Question

I have a simple table inside a MySQL 8.0 database like this:

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

I can populate my JSONField using:

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

And what I want is to extract the SUM of all Balance values.

I tried this:

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

But gives the result:

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

Also, if I get rid of the SUM, the result is:

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

Which makes me believe that JSON_UNQUOTE is not working with this nested array SELECT as well.

How can I query the table (with no custom functions preferably) so that it gives:

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

Kiran Muralee · Accepted Answer · 2018-10-25 19:54:47Z

4

In order to get the desired result, you can make use of JSON Table Functions in MySQL 8.0. It converts JSON data into tabular form.Then onward you can use aggregate function on result.

The query to achieve the same is given below

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

The DB fiddle link is given below
https://www.db-fiddle.com/f/vrn55vY2TMeVsVAzy9CS1w/11

More information on JSON Table Functions can be found below
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

answered Oct 25, 2018 at 19:54

Kiran Muralee

2,0802 gold badges19 silver badges28 bronze badges

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

1 Comment

drec4s Over a year ago

Works perfectly. Thank you!

Collectives™ on Stack Overflow

MySQL JSONField Nested Array SUM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related