MySQL Labs JSON native type: How SUM the result of an array returned by jsn_extract?

Question

Below follow my scenario:

CREATE TABLE `CustomerOrder` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`id`)
);

And we can use this Customer Order json as example:

{ 
    "creation": "2015-07-30 14:27:51",
    "customer": {
        "id": 2,
        "email": "[email protected]"
    },
    "item": [
        {
            "sku": 182,
            "unitPrice": 0.89,
            "qty": 10
        }, {
            "sku": 712,
            "unitPrice": 12.99,
            "qty": 2
        }
    ]
}

Runing on MySQL console this SQL:

SELECT json_extract(data, '$.item[*].unitPrice') AS price FROM CustomerOrder;

I will have this output:

[ 0.89, 12.99 ]

Now how can I evaluate a SUM of [0.89 + 12.99] or 1..N elements of items?

For my tests I used this version of MySQL Labs:

http://downloads.mysql.com/snapshots/pb/mysql-5.7.7-labs-json/mysql-5.7.7-labs-json-linux-el6-x86_64.tar.gz

http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

Answer 1

I tried to use @Rick's answer but it didn't work for me. So I digged mysql documentation for mysql functions. Here's the working function for mysql 5.7.14,

create function sum_array_cells( input_array json )
returns double
BEGIN
    DECLARE array_length INTEGER(11);
    DECLARE retval DOUBLE(19,2);
    DECLARE cell_value DOUBLE(19,2);
    DECLARE idx INT(11);

    SELECT json_length( input_array ) INTO array_length;

    SET retval = 0.0;

    SET idx = 0;

    WHILE idx < array_length DO
        SELECT json_extract( input_array, concat( '$[', idx, ']' ) )
            INTO cell_value;

        SET retval = retval + cell_value;
        SET idx = idx + 1;
    END WHILE;

    RETURN retval;
END

Then use the function as @Rick wrote:

select sum_array_cells( '[ 0.89, 12.99, 5.23, 2.04 ]' );

Answer 2

Use JSON_TABLE see example:

SET @datax = '[
  { "product":"apple",  "price": 5}, 
  { "product":"banana",  "price": 7}
]';


SELECT price.* 
FROM 
     JSON_TABLE(@datax, '$[*]' COLUMNS (
                price INTEGER PATH '$.price')
     ) price;`

Then, add up the price.