I'd like to sum up certain values from a JSON snippet following this example data:

set @json='
{
  "items": [
    {
      "a": {
        "a_amount": "0.0020095"
      },
      "b": {
        "b_amount": "0.0004"
      }
    },
    {
      "a": {
        "a_amount": "0.02763081"
      },
      "b": {
        "b_amount": "0.0055"
      }
    }
  ]
}';

I need to sum all a.a_amount and all b.b_amount independantly, so I'd like to do something like SUM(a.a_amount) and SUM(b.b_amount).

But I haven't gotten any further than extracting the respective values like this:

SELECT JSON_EXTRACT(@json, '$.items[*].a.a_amount') AS sum_a,
       JSON_EXTRACT(@json, '$.items[*].b.b_amount') AS sum_b;

I've fiddled around with JSON_EXTRACT(), JSON_VALUE() and even the ha_connect plugin but still couldn't come up with SQL code that would give me the sums I need.

Who can help me here?

CodePudding user response：

The JSON Table function could help you. Here a small example with your data. Maybe you must play a little bit with the data types.

SELECT 
  SUM(a_amount), 
  SUM(b_amount) 
FROM
(
  SELECT * FROM 
  JSON_TABLE(@json, '$.items[*]' COLUMNS(
      a_amount FLOAT PATH '$.a.a_amount',
      b_amount FLOAT PATH '$.b.b_amount'
    )
  ) as items
) as temp;

View on DB Fiddle

CodePudding user response：

One option is using a DOUBLE conversion along with Recursive CTE through use of JSON_EXTRACT() function such as

WITH RECURSIVE cte AS
(
SELECT 0 i
UNION ALL
SELECT i   1 i
  FROM cte
 WHERE i   1 <= ( SELECT JSON_LENGTH(json) FROM j )  
)
SELECT SUM(CAST(JSON_EXTRACT(json, CONCAT('$.items[',i,'].a.a_amount')) AS DOUBLE)) AS sum_a,
       SUM(CAST(JSON_EXTRACT(json, CONCAT('$.items[',i,'].b.b_amount')) AS DOUBLE)) AS sum_b
  FROM cte,
       j

Demo