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 |
+-----------+
Follow
asked Oct 24, 2018 at 11:39
7,75388 gold badges2929 silver badges4949 bronze badges
1 Answer
Sorted by:
Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first)
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
DB Fiddle - SQL Database Playground
More information on JSON Table Functions can be found below
MySQL :: MySQL 8.0 Reference Manual :: 12.18.6 JSON Table Functions
Follow
SELECT sum(result) as result
FROM test,
JSON_TABLE(
`id`,
'$.cccc[*]' COLUMNS(
NESTED PATH '$.age' COLUMNS (result DECIMAL PATH '$')
)
) AS jt;