Question 167917 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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

Table schema

The table has a column features of type JSON with 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?
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search