FOREACH

description

Syntax

AGGREGATE_FUNCTION_FOREACH(arg...) Converts an aggregate function for tables into an aggregate function for arrays that aggregates the corresponding array items and returns an array of results. For example, sum_foreach for the arrays [1, 2], [3, 4, 5]and[6, 7]returns the result [10, 13, 5] after adding together the corresponding array items.

example

  1. mysql [test]>select a , s from db;
  2. +-----------+---------------+
  3. | a         | s             |
  4. +-----------+---------------+
  5. | [1, 2, 3] | ["ab", "123"] |
  6. | [20]      | ["cd"]        |
  7. | [100]     | ["efg"]       |
  8. | NULL      | NULL          |
  9. | [null, 2] | [null, "c"]   |
  10. +-----------+---------------+
  12. mysql [test]>select sum_foreach(a) from db;
  13. +----------------+
  14. | sum_foreach(a) |
  15. +----------------+
  16. | [121, 4, 3]    |
  17. +----------------+
  19. mysql [test]>select count_foreach(s) from db;
  20. +------------------+
  21. | count_foreach(s) |
  22. +------------------+
  23. | [3, 2]           |
  24. +------------------+
  26. mysql [test]>select array_agg_foreach(a) from db;
  27. +-----------------------------------+
  28. | array_agg_foreach(a)              |
  29. +-----------------------------------+
  30. | [[1, 20, 100, null], [2, 2], [3]] |
  31. +-----------------------------------+
  33. mysql [test]>select map_agg_foreach(a,a) from db;
  34. +---------------------------------------+
  35. | map_agg_foreach(a, a)                 |
  36. +---------------------------------------+
  37. | [{1:1, 20:20, 100:100}, {2:2}, {3:3}] |
  38. +---------------------------------------+

keywords

FOREACH