opensearch-project / opensearch-project/sql

[FEATURE] Add ARRAY_TO_CSV helper function

Open
#4,587 5 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

calcite feature PPL
Dominant language
Java
Stars
176
Forks
229
Avg merge
2d 21h
Merged PRs (30d)
43

Description

Is your feature request related to a problem?
Using PPL 3.4.0, it would be very convenient to have an array helper function to flatten an array content into a CSV string.

As of 2025-10-16, the PPL Collection Functions has a REDUCE function which can fulfill this task. However it is rather clumsy.

Example:

source=security-auditlog-2024.05
| eval myArray=array('GET', 'READ', 'WRITE')
| eval myArray_CSV = reduce(myArray, '', (acc, x) -> CONCAT_WS(',', acc, x))
| fields myArray, myArray_CSV
| head 1

The output looks like (notice the , prefix in the CSV result ,GET,READ,WRITE)

{
  "schema": [
    {
      "name": "myArray",
      "type": "array"
    },
    {
      "name": "myArray_CSV",
      "type": "string"
    }
  ],
  "datarows": [
    [
      [
        "GET",
        "READ",
        "WRITE"
      ],
      ",GET,READ,WRITE"
    ]
  ],
  "total": 1,
  "size": 1
}

What solution would you like?
It would be more convenient to have a utility function named ARRAY_TO_CSV to build the CSV string. The above query would have a more user-friendly syntax.

source=security-auditlog-2024.05
| eval myArray=array('GET', 'READ', 'WRITE')
| eval myArray_CSV = ARRAY_TO_CSV(myArray)
| fields myArray, myArray_CSV
| head 1

Furthermore, the ARRAY_TO_CSV should accept and optional 2nd parameter which is the delimiter string. This would allow a better formatting of the CSV string. The default value as , (comma, no space)

// CSV with comma-space as separator
ARRAY_TO_CSV(myArray, ", ")  -> "GET, READ, WRITE"

// CSV with space-pipe-space as separator
ARRAY_TO_CSV(myArray, " | ")  -> "GET | READ | WRITE"

What alternatives have you considered?
Use the REDUCE function, which has 2 inconveniences:

  • complicate syntax
  • resulting CSV always has an extra separator string, either at the beginning or the end.

Do you have any additional context?
The ARRAY_TO_CSV function if available, would help to improve the output of the collector operators TAKE, LIST, VALUES of the stats command.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the collection functions documentation in docs/user/ppl/functions/collection.rst and compare the requested behavior with REDUCE, then review the related stats documentation in docs/user/ppl/cmd/stats.rst. Done means ARRAY_TO_CSV accepts an array, uses a comma by default, supports an optional delimiter, and produces the demonstrated strings without an extra separator.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.