arraystring

Use the `arraystring()` function to convert an array into a single string by joining its elements with a specified delimiter.

## Syntax

```sql
arraystring (<array>, <delimiter>)
```

## Parameters

| Name | Type | Required | Description |
| ----------- | ------ | -------- | ---------------------------------------------------------------------------- |
| `array` | array | Yes | The array field whose elements you want to concatenate into a single string. |
| `delimiter` | string | Yes | A string literal that will be used to join the elements of the array. |

## Returns

The `arraystring()` function returns a single string where each element of the original array is separated by the specified delimiter.

## Usage notes

* The function requires an existing XQL-native array field as input.
* The function operates on XQL arrays and implicitly converts elements to strings for concatenation.
* The specified delimiter is inserted between each element of the original array in the resulting string.
* If the input array is empty (for example, `[]`), the function returns an empty string (`""`).
* This function is typically used within the `alter` stage to create new fields or modify existing ones, but can also be used in `filter` stages.

## Examples

### Example 1: Converting a string array to a string with a comma and space delimiter

**Goal**: Convert an array of strings into a single string, separating each element with a comma and a space.

**XQL code**:

```sql
config timeframe = 1d 
| dataset = sample_xql_raw 
| alter combined_tags = arraystring(string_tags, ", ") 
| fields event_id, string_tags, combined_tags 
| limit 3 
```

**Explanation**: The `string_tags` field contains arrays of strings. The `arraystring()` function converts each array into a single string, using `", "` as the separator between elements.

**Output**:

| EVENT\_ID | STRING\_TAGS | COMBINED\_TAGS |
| --------- | --------------------------- | ---------------------- |
| 101 | \["security", "login"] | "security, login" |
| 102 | \["filesystem", "critical"] | "filesystem, critical" |
| 103 | \["network", "cloud"] | "network, cloud" |

### Example 2: Converting a numeric array to a string with a pipe delimiter

**Goal**: Join elements of a numeric array into a single string separated by a pipe character.

**XQL code**:

```sql
config timeframe = 1d 
| dataset = sample_xql_raw 
| alter numeric_codes_as_strings = arraymap(numeric_codes, to_string("@element")) 
| alter combined_codes = arraystring(numeric_codes_as_strings, " | ") 
| fields event_id, numeric_codes, numeric_codes_as_strings, combined_codes 
| limit 3 
```

**Explanation**: The `numeric_codes` field contains arrays of integers. First, `arraymap()` iterates through each number and converts it to a string using `to_string()`, creating a new array `numeric_codes_as_strings`. Then, `arraystring()` concatenates the elements of this string array using the specified `" | "` delimiter.

**Output**:

| EVENT\_ID | NUMERIC\_CODES | NUMERIC\_CODES\_AS\_STRINGS | COMBINED\_CODES |
| --------- | -------------------------- | -------------------------------------- | --------------- |
| 101 | \[13, -47, 29, 82, -15] | \["13", "-47", "29", "82", "-15"] | 13 |
| 102 | \[-21, 56, 13, -88, 42] | \["-21", "56", "13", "-88", "42"] | -21 |
| 103 | \[90, -33, 7, 51, -62, 18] | \["90", "-33", "7", "51", "-62", "18"] | 90 |

### Example 3: Handling an empty array input

**Goal**: Demonstrate the behavior of the function when the input array is empty.

**XQL code**:

```sql
config timeframe = 1d 
| dataset = sample_xql_raw 
| filter event_id = 104 
| alter empty_array_to_string = arraystring(array_of_json_objects, "-") 
| fields event_id, array_of_json_objects, empty_array_to_string 
| limit 1 
```

**Explanation**: When `arraystring()` is applied to an empty array (`[]`), it returns an empty string (`""`), as there are no elements to join.

**Output**:

| EVENT\_ID | ARRAY\_OF\_JSON\_OBJECTS | EMPTY\_ARRAY\_TO\_STRING |
| --------- | ------------------------ | ------------------------ |
| 104 | \[] | "" |

### Example 4: Converting a dynamically sliced array to a string

**Goal**: Apply the function to a portion of a numeric array created using `arrayrange`.

**XQL code**:

```sql
config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_two_numeric_codes = arrayrange(numeric_codes, 0, 2) 
| alter sliced_codes_as_strings = arraymap(first_two_numeric_codes, to_string("@element")) 
| alter sliced_codes_string = arraystring(sliced_codes_as_strings, ", ") 
| fields event_id, numeric_codes, first_two_numeric_codes, sliced_codes_as_strings, sliced_codes_string 
| limit 3 
```

**Explanation**: First, `arrayrange(numeric_codes, 0, 2)` extracts the first two elements of `numeric_codes`. Then, `arraymap()` converts these numeric elements to strings. Finally, `arraystring()` concatenates these string elements using `", "` as the delimiter.

**Output**:

| EVENT\_ID | NUMERIC\_CODES | FIRST\_TWO\_NUMERIC\_CODES | SLICED\_CODES\_AS\_STRINGS | SLICED\_CODES\_STRING |
| --------- | -------------------------- | -------------------------- | -------------------------- | --------------------- |
| 101 | \[13, -47, 29, 82, -15] | \[13, -47] | \["13", "-47"] | "13, -47" |
| 102 | \[-21, 56, 13, -88, 42] | \[-21, 56] | \["-21", "56"] | "-21, 56" |
| 103 | \[90, -33, 7, 51, -62, 18] | \[90, -33] | \["90", "-33"] | "90, -33" |

### Example 5: Using arraystring() in a filter stage

**Goal**: Filter records by creating a string from an array and checking for a specific substring.

**XQL code**:

```sql
config timeframe = 1d 
| dataset = sample_xql_raw 
| alter combined_tags_for_filter = arraystring(string_tags, "-") 
| filter combined_tags_for_filter contains "security" 
| fields event_id, string_tags, combined_tags_for_filter 
| limit 3 
```

**Explanation**: The `combined_tags_for_filter` field is created by joining `string_tags` elements with a hyphen. The `filter` stage then checks if this new string field contains the substring "security". Only Event IDs containing "security" in their tags are returned.

**Output**:

| EVENT\_ID | STRING\_TAGS | COMBINED\_TAGS\_FOR\_FILTER |
| --------- | ----------------------- | --------------------------- |
| 101 | \["security", "login"] | "security-login" |
| 106 | \["security", "attack"] | "security-attack" |

### Example 6: Converting application transitions to a delimited string and deduplicating

**Goal**: Retrieve non-null application ID transitions, convert the transition arrays into strings delimited by " : ", and remove duplicate transition strings based on the insertion time.

**XQL code**:

```sql
config timeframe = 1d 
| dataset = sample_xql_raw 
| fields action_app_id_transitions as aait 
| alter transitions_string = arraystring(aait, " : ") 
| dedup transitions_string by asc _time 
| filter aait != null
```

**Explanation**: The query first filters the sample\_xql\_raw dataset to include only records where action\_app\_id\_transitions contains data. The query renames the field to aait for brevity. The arraystring() function then takes each array of transitions and joins the elements into a single string using " : " as the separator. Finally, the dedup stage ensures that only unique transition strings are retained, ordered by the timestamp.

**Output**:

| aait | transitions\_string |
| ------------------------------- | -------------------------- |
| \["App\_1", "App\_2", "App\_3"] | "App\_1 : App\_2 : App\_3" |
| \["App\_A", "App\_B"] | "App\_A : App\_B" |
| \["App\_1", "App\_5"] | "App\_1 : App\_5" |

## Related articles

* **Stages**: [`alter`](../stages/alter), [`config`](../stages/config), [`fields`](../stages/fields), [`filter`](../stages/filter), [`limit`](../stages/limit)
* **Functions**: [`arraymap`](arraymap), [`arrayrange`](arrayrange), [`to_string`](to_string)