> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/coalesce.md).

# coalesce

Use the `coalesce()` function to return the first non-NULL value from a defined list of input fields or expressions.

## Syntax

```sql
coalesce (<field_1>, <field_2>, ...<field_n>)
```

## Parameters

| Name      | Type | Required | Description                                                                                   |
| --------- | ---- | -------- | --------------------------------------------------------------------------------------------- |
| `field_n` | Any  | Yes      | An arbitrary number of fields or expressions. The function evaluates them from left to right. |

## Returns

The `coalesce()` function returns a single value whose data type matches the first non-NULL argument it encounters. If all arguments are `NULL`, the function returns `NULL`.

## Usage notes

* Arguments are evaluated strictly from left to right. When a non-NULL value is found, the evaluation stops, and that value is returned.
* This function is valuable for ensuring data completeness and providing fall-back mechanisms, allowing you to define a prioritized list of data sources for a single field.

## Examples

### Example 1: Coalescing literal values of different types

**Goal**: Demonstrate the core "first non-NULL" behavior by providing various literal values, including NULLs, of different data types.

**XQL code**:

```sql
config timeframe = 1d // Sets the query timeframe 
| dataset = sample_xql_raw // Specifies the dataset to use 
| alter chosen_value_str = coalesce(NULL, "Fallback String", "Another Option") // First non-NULL string 
| alter chosen_value_int = coalesce(NULL, 123, NULL, 456) // First non-NULL integer 
| alter chosen_value_bool = coalesce(false, NULL, true) // First non-NULL boolean 
| fields event_id, chosen_value_str, chosen_value_int, chosen_value_bool 
| limit 3 
```

**Explanation**: For `chosen_value_str`, `coalesce()` skips `NULL` and returns "Fallback String". For `chosen_value_int`, it returns `123`. For `chosen_value_bool`, it returns `false`. This demonstrates the function's ability to handle various data types and select the first available non-NULL literal.

**Output**:

| EVENT\_ID | CHOSEN\_VALUE\_STR | CHOSEN\_VALUE\_INT | CHOSEN\_VALUE\_BOOL |
| --------- | ------------------ | ------------------ | ------------------- |
| 101       | Fallback String    | 123                | false               |
| 102       | Fallback String    | 123                | false               |
| 103       | Fallback String    | 123                | false               |

### Example 2: Coalescing existing fields

**Goal**: Use `coalesce()` with existing fields, specifically leveraging a field that can be NULL (`dst_domain`) and one that is always present (`event_description`) as a fallback.

**XQL code**:

```sql
config timeframe = 1d // Sets the query timeframe 
| dataset = sample_xql_raw // Specifies the dataset to use 
// dst_domain is NULL for event_id 105, event_description is always present 
| alter primary_or_fallback_description = coalesce(dst_domain, event_description) 
| fields event_id, dst_domain, event_description, primary_or_fallback_description 
| limit 5 
```

**Explanation**: For events where `dst_domain` is not `NULL` (for example, event 101-104), `coalesce()` returns the `dst_domain`. For event 105, where `dst_domain` is `NULL`, `coalesce()` falls back to `event_description`, providing "Data transformation".

**Output**:

| EVENT\_ID | DST\_DOMAIN                             | EVENT\_DESCRIPTION             | PRIMARY\_OR\_FALLBACK\_DESCRIPTION      |
| --------- | --------------------------------------- | ------------------------------ | --------------------------------------- |
| 101       | ec2.amazonaws.com                       | User login successful          | ec2.amazonaws.com                       |
| 102       | sts.amazonaws.com                       | File access attempt            | sts.amazonaws.com                       |
| 103       | [www.google.com](http://www.google.com) | Network connection established | [www.google.com](http://www.google.com) |
| 104       | dropbox.com                             | System heartbeat               | dropbox.com                             |
| 105       | NULL                                    | Data transformation            | Data transformation                     |

### Example 3: Coalescing multiple JSON paths for a single concept

**Goal**: Handle variations in JSON data where different keys might represent the same logical piece of information (for example, status code under `code` or `error`).

**XQL code**:

```sql
config timeframe = 1d // Sets the query timeframe 
| dataset = sample_xql_raw // Specifies the dataset to use 
// Extract 'code' or 'error' from JSON, using the first available 
| alter status_code = coalesce(simple_json_data -> code, simple_json_data -> error) 
| fields event_id, simple_json_data, status_code 
| limit 3 
```

**Explanation**: For event 101, `code` (200) is found first. For event 102, `code` is not present, so `error` ("access\_denied") is returned. For event 103, neither `code` nor `error_code` exists, resulting in `NULL`.

**Output**:

| EVENT\_ID | SIMPLE\_JSON\_DATA                                | STATUS\_CODE   |
| --------- | ------------------------------------------------- | -------------- |
| 101       | {"status": "ok", "code": 200}                     | 200            |
| 102       | {"status": "fail", "error": "access\_denied"}     | access\_denied |
| 103       | {"connection\_id": "CONN-001", "protocol": "TCP"} | NULL           |

### Example 4: Coalescing complex nested JSON paths for a unified identifier

**Goal**: Extract a "principal name" from multiple possible nested JSON paths within the `nested_json_data` field, such as `user.name`, `process.name`, `client.id`, or `db.name`.

**XQL code**:

```sql
config timeframe = 1d // Sets the query timeframe 
| dataset = sample_xql_raw // Specifies the dataset to use
| filter event_id in(101, 102, 108, 109, 110)
// Attempt to find a 'principal_name' from various nested JSON paths 
| alter principal_name = coalesce( 
    nested_json_data -> user.name, 
    nested_json_data -> process.name, 
    nested_json_data -> client.id, 
    nested_json_data -> db.name 
) 
| fields event_id, nested_json_data, principal_name 
| limit 5 
```

**Explanation**: For event 101, `user.name` ("Alice") is found first. For event 102, `user.name` is missing, but `process.name` ("cmd.exe") is present. For event 108, none of the specified paths exist, resulting in `NULL`. For event 109, `client.id` ("C2") is found. For event 110, `db.name` ("prod\_db") is found.

**Output**:

| EVENT\_ID | NESTED\_JSON\_DATA                                                                                 | PRINCIPAL\_NAME |
| --------- | -------------------------------------------------------------------------------------------------- | --------------- |
| 101       | {"user": {"id": "U1", "name": "Alice"}, "session": {"start": "10:00", "type": "web"}}              | Alice           |
| 102       | {"process": {"name": "cmd.exe", "pid": 1234}, "target": {"path": "/var/log", "permission": "rwx"}} | cmd.exe         |
| 108       | {"system":{"hostname":"webserver01","os":"Linux"},"patch":{"version":"1.2.3"}}                     | NULL            |
| 109       | {"client":{"id":"C2","api\_key":"xyz"},"request":{"endpoint":"/data","rate":100}}                  | C2              |
| 110       | {"db":{"name":"prod\_db","type":"SQL"},"storage":{"location":"S3","cost\_usd":15}}                 | prod\_db        |

### Example 5: Selecting the first non-null username from multiple fields

**Goal**: Evaluate three different username fields (actor\_primary\_username, os\_actor\_primary\_username, and causality\_actor\_primary\_username) and return the first available (non-null) value to populate a single username column.

**XQL code**:

```sql
|dataset = sample_xql_raw
| fields actor_primary_username,
       os_actor_primary_username,
       causality_actor_primary_username 
| alter username = coalesce(actor_primary_username,
                          os_actor_primary_username,
                          causality_actor_primary_username) 
```

**Explanation**: The coalesce() function checks the provided arguments in order from left to right. The function returns the value of the first field that is not null. If actor\_primary\_username is null, it checks os\_actor\_primary\_username, and so on. This is a common technique for normalizing data when the same information might be stored in different fields depending on the event source.

**Output**:

| actor\_primary\_username | os\_actor\_primary\_username | causality\_actor\_primary\_username | username      |
| ------------------------ | ---------------------------- | ----------------------------------- | ------------- |
| null                     | "admin\_user"                | "system"                            | "admin\_user" |
| "jsmith"                 | "jsmith\_os"                 | null                                | "jsmith"      |
| null                     | null                         | "root"                              | "root"        |
| null                     | null                         | null                                | null          |

## Related articles

* **Stages**: [`alter`](/xql-command-reference-guide/readme/stages/alter.md), [`config`](/xql-command-reference-guide/readme/stages/config.md), [`fields`](/xql-command-reference-guide/readme/stages/fields.md), [`limit`](/xql-command-reference-guide/readme/stages/limit.md), [`filter`](/xql-command-reference-guide/readme/stages/filter.md)
* **Functions**: [`if`](/xql-command-reference-guide/readme/functions/if.md)
* **Datasets**: [`xdr_data`](https://www.google.com/search?q=%5Bhttps://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction%5D\(https://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction\))


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/coalesce.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.