Find out the sum of all the elements in json array in mysql with filtering

Question

I am using mariadb version 10.2.43-MariaDB-1:10.2.43+maria~bionic

Table schema

The table has a columns id attribute and details array with the following two rows of data :

{
  "id": 9,
  "details": [
    {
      "amount": 100,
      "flag": true,
      "fieldA": "abcd"
    },
    {
      "amount": 101,
      "flag": false,
      "fieldB": "bcde"
    },
    {
      "amount": 103,
      "flag": true,
      "fieldA": "abcd"
    }
  ]
},
{
  "id": 10,
  "details": [
    {
      "amount": 110,
      "flag": false,
      "fieldA": "abcd"
    },
    {
      "amount": 102,
      "flag": true,
      "fieldB": "bcde"
    }
  ]
}

I want to calculate the sum of amounts when the flag is true.

• For id = 9 amounts = [100, 103]
• For id = 10 amounts = [102]
• Total = 100 + 103 + 102 = 305

On searching online I found a few answers suggesting json_table but we are using older version of mariadb which does not support json_table.

I have used this way to extract the amounts and flags and then finally handled the sum in code.

SELECT JSON_EXTRACT(features,'$. details[*].amount') as amounts,
       JSON_EXTRACT(features,'$.details[*].flag') as flag 
  FROM table
 WHERE JSON_EXTRACT(features,'$.details[*].flag') != 'NULL';

Output

-------------------------------------------------------------+---------------------------------------------------------------------+
| [500000, 1000000]                                           | [false, false]                                                      |
| [1100000]                                                   | [false]                                                             |
| [1000000]                                                   | [false]                                                             |
| [500000, 1000000]                                           | [false, false]                                                      |
| [100000]                                                    | [false]                                                             |
| [5000000]                                                   | [false]                                                             |                                                       |
| [50000]                                                     | [false]                                                             |
| [500000]                                                    | [false]                                                             |
| [500000]                                                    | [false]                                                             |
| [10000]                                                     | [true]                                                              |
| [49998]                                                     | [true]                                                              |
| [600000, 399980]                                            | [false, true]    

Questions:

1. I want to know if the order of elements in amounts array and flag array will be the same as that in details. (otherwise the sum I am calculating will be wrong).
2. Is there a more efficient way to calculate the sum without using code?

Answer 1

1. I want to know if the order of elements in amounts array and flag array will be the same as that in details. (otherwise the sum I am calculating will be wrong).

The documentation says:

Extracts data from a JSON document. [...]. Returns all matched values; either as a single matched value, or, if the arguments could return multiple values, a result autowrapped as an array in the matching order.

So yes, both calls to json_extract will give you an ordered slice of the json array. But note that, as far as your query goes, you are still left with the task of unesting each slice (the two arrays that you are getting)... which is the core of the task, and brings us to your second question.

---

2. Is there a more efficient way to calculate the sum without using code?

MariaDB does not support json_table, which can expand a json array to rows. But we can emulate it with the help of a table of numbers and of other MariaDB json functions.

There are many different ways that you can create a table of numbers, either inline in the query or permanently stored in a table. Here I am just using a fixed list of a few numbers :

select sum( json_value( t.details, concat('$[', n.i, '].amount') ) ) total_amount
from mytable t
inner join (select 0 as i union all select 1 union all select 2) n 
    on n.i < json_length(t.details)
where json_value( t.details, concat('$[', n.i, '].flag') ) 

The join on n generate one row for each item in the JSON array; given the index of the element, we can generate the proper json path to its amount and flag (that's what the concat()s do), and access the values with json_value().

I don't have a MariaDB 10.2 database at hand to test, but here is a MariaDB 10.3 fiddle. Note the database properly recognizes the boolean value in the flag attribute, which simplifies the where clause.

Answer 2

One option is to use JSON_EXTRACT() function while looping through by Recursive CTE which's supported for 10.2.2 + as in the following query

WITH RECURSIVE cte AS
(
 SELECT 0 i
 UNION ALL
 SELECT i + 1 i
   FROM cte
  WHERE i + 1 <= ( SELECT MAX(JSON_LENGTH(features, '$.details')) FROM t ) 
)
SELECT SUM(CASE 
           WHEN JSON_EXTRACT(features, CONCAT('$.details[',i,'].flag')) = 'true' THEN
                CAST(JSON_EXTRACT(features, CONCAT('$.details[',i,'].amount')) AS DOUBLE)
            END) AS sum_amount 
  FROM cte,
       t

Demo