REDUCE

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

This function takes an initial value, an array, and a scalar expression (accumulator expression) as inputs. The initial value is assigned to the accumulator when the accumulator expression is applied to the first element in the array. The accumulator expression is built using the REDUCE_ACC() and REDUCE_VALUE() functions which serve as the placeholder for the accumulator and the current value, respectively. The accumulator expression is iteratively applied to each element of the array, starting at index 0.

Note: This function does not directly accept or return JSON data. However, it is frequently used with JSON data, especially after converting a JSON array to a SingleStore array with JSON_TO_ARRAY(). Refer to Examples.

Syntax

REDUCE(<initial_value>, <array_input>, <accumulator_expression>)

Arguments

initial_value: Any literal, variable, or expression that evaluates to a scalar data type.
array_input: An array of scalar values or any expression that resolves to an array of scalar values.
accumulator_expression: Any literal, variable, or expression that evaluates to a scalar data type. This argument supports following placeholder functions:
- REDUCE_ACC(): Placeholder function for the accumulator.
- REDUCE_INDEX(): Placeholder function that returns the index of the current iteration in the array. The array uses a 0-based index.
- REDUCE_VALUE(): Placeholder function that returns the current value.

Remarks

The data type of initial_value determines the data type of each iteration, and consequently, the data type of the result. If NULL is passed as the initial_value, the array type determines the data type of the result. You can also explicitly typecast the data type of initial_value to another type to modify the data type of the result.
If the specified array is empty, the initial_value is returned as the result.
Aggregate functions, non-scalar functions, and sub-selects are not supported inside the accumulator expression.

Examples

Here are some examples:

Add all the elements in an array:

SQL

SELECT REDUCE(
  0,
  JSON_TO_ARRAY('[1,2,3,4]'),
  REDUCE_ACC() + REDUCE_VALUE()
) AS Result;

+--------+
| Result |
+--------+
|     10 |
+--------+

Apply a mapping to an array, which multiplies each element in the array by 10:

SQL

SELECT REDUCE(
  '[]':>JSON,
  JSON_TO_ARRAY('[1,2,3,4]'),
  JSON_ARRAY_PUSH_JSON(REDUCE_ACC(),REDUCE_VALUE()*10)
) AS Result;

+---------------+
| Result        |
+---------------+
| [10,20,30,40] |
+---------------+

Implement a filter and return odd numbers in an array:

SQL

SELECT REDUCE(
  '[]':>JSON,
  JSON_TO_ARRAY('[1,2,3,4]'),
  IF(REDUCE_VALUE()%2!=0,JSON_ARRAY_PUSH_JSON(REDUCE_ACC(),REDUCE_VALUE()),REDUCE_ACC())) AS Result;

+--------+
| Result |
+--------+
| [1,3]  |
+--------+

Return the index of the first occurrence of the number 3 in an array:

SQL

SELECT REDUCE(
-1,
JSON_TO_ARRAY('[1,2,3,4,3]'),
IF(REDUCE_ACC() < 0 && REDUCE_VALUE()<=>'3',REDUCE_INDEX(),REDUCE_ACC())) AS Result;

+--------+
| Result |
+--------+
|      2 |
+--------+

If the array doesn't contain the search value, the initial value is returned instead.

SQL

SELECT REDUCE(
  -1,
  JSON_TO_ARRAY('[1,2,4]'),
  IF(REDUCE_ACC() < 0 && REDUCE_VALUE()<=>'3',REDUCE_INDEX(),REDUCE_ACC())) AS Result;

+--------+
| Result |
+--------+
|     -1 |
+--------+

Return the index of all the occurrences in the array:

SQL

SELECT REDUCE(
  '[]':>JSON,
  JSON_TO_ARRAY('[1,2,3,4,3]'),
  IF(NOT REDUCE_VALUE() IN (3),REDUCE_ACC(),JSON_ARRAY_PUSH_JSON(REDUCE_ACC(),REDUCE_INDEX())
)) As Result;

+--------+
| Result |
+--------+
| [2,4]  |
+--------+

Concatenate multiple arrays:

SQL

SET @INPUT = '[["a","b"],["c","d","e"],["f","g"]]';
SELECT REDUCE(
  '[]':>JSON,
  JSON_TO_ARRAY(@INPUT),
  REDUCE(
    REDUCE_ACC(),JSON_TO_ARRAY(REDUCE_VALUE()),JSON_ARRAY_PUSH_JSON(REDUCE_ACC(),REDUCE_VALUE())
)) As Result;

+-------------------------------+
| Result                        |
+-------------------------------+
| ["a","b","c","d","e","f","g"] |
+-------------------------------+

Concatenate all the elements in an array:

SQL

SELECT REDUCE(
  "",
  ["A","B","C"]:>ARRAY(TEXT NOT NULL),
  CONCAT(REDUCE_ACC(), REDUCE_VALUE()) ) AS Result;

+--------+
| Result |
+--------+
| ABC    |
+--------+

The following example aggregates the prices and returns the total price for each product_id.

SQL

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    prices JSON
);

INSERT INTO orders (order_id, product_id, quantity, prices) VALUES
(1, 101, 5, '[10, 20, 30, 40, 50]'),
(2, 102, 3, '[15, 25, 35]'),
(3, 101, 2, '[10, 20]'),
(4, 103, 4, '[30, 40, 50, 60]'),
(5, 102, 6, '[15, 25, 35, 45, 55, 65]'),
(6, 101, 3, '[10, 20, 30]'),
(7, 104, 7, '[20, 30, 40, 50, 60, 70, 80]'),
(8, 103, 2, '[30, 40]'),
(9, 102, 4, '[15, 25, 35, 45]'),
(10, 101, 1, '[10]');

SELECT product_id, SUM(REDUCE(
  0, 
  JSON_TO_ARRAY(prices), 
  REDUCE_ACC() + REDUCE_VALUE())) AS "Total Price"
FROM orders GROUP BY product_id;

+------------+-------------+
| product_id | Total Price |
+------------+-------------+
|        101 |         250 |
|        102 |         435 |
|        103 |         250 |
|        104 |         350 |
+------------+-------------+

Convert an ARRAY of arbitrary type to a JSON array:

SQL

SELECT REDUCE(
  '[]':>JSON,
  ['apple', 'banana', 'cherry', 'date', 'elderberry', 'fig']:>ARRAY(LONGTEXT), 
  JSON_ARRAY_PUSH_JSON(REDUCE_ACC(), TO_JSON(REDUCE_VALUE()))
) AS Result;

+-------------------------------------------------------+
| Result                                                |
+-------------------------------------------------------+
| ["apple","banana","cherry","date","elderberry","fig"] |
+-------------------------------------------------------+