# REDUCE

`REDUCE()` is a scalar function that applies an expression to each element of an array and returns a single value as the result. It allows you to aggregate multiple values in an array into a single value.

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](https://docs.singlestore.com/#section-idm4540271205028834203034915626.md).

## 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;

  ```
  ```output

  +--------+
  | 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;

  ```
  ```output

  +---------------+
  | 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;

  ```
  ```output

  +--------+
  | 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;

  ```
  ```output

  +--------+
  | 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;

  ```
  ```output

  +--------+
  | 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;

  ```
  ```output

  +--------+
  | 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;

  ```
  ```output

  +-------------------------------+
  | 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;

  ```
  ```output

  +--------+
  | 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;

  ```
  ```output

  +------------+-------------+
  | 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;

  ```
  ```output

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

***

Modified at: September 26, 2024

Source: [/db/v9.1/reference/sql-reference/json-functions/reduce/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/reduce/)

(An index of the documentation is available at /llms.txt)