count (comp) ↗

Use the count() function within a comp stage to calculate and return the number of non-null values found for a specified field, or the total number of rows if no field is specified, over a group of rows.

Syntax

comp count([ <field>]) [as <alias>] [by <field1>[,<field2>...]] [addrawdata = true|false [as <target field>]]

Parameters

Name Type Required Description
field string, integer, float, boolean No The field to count non-null values for.
alias string No The alias name of the field, using the as clause.
field1, field2 string, integer, float, boolean No The fields used to group rows in the by clause.
addrawdata boolean No When set to true, introduces a raw_data column into the output, listing the raw data events that contributed to the aggregate result.
target field string No The alias name for the raw data column when addrawdata is set to true.

Returns

The count() function returns a single count value representing the number of rows or non-null values.

Usage Notes

  • When a field is provided, it exclusively counts non-null values.
  • Without a specified field, it calculates and returns the total number of rows, including those with null values.
  • The comp stage or the windowcomp stage must always precede count().
  • New columns generated by the comp stage (including the count itself) are typically appended as the last columns in the result set.
  • Any other fields not explicitly included in the by clause or as part of a calculated column will be removed from the result set, including all system fields.
  • When addrawdata is true, the query processes up to 50 defined fields and displays up to 100 events.

Examples

Example 1: Counting all rows across the entire dataset (no field specified)

Goal: Compute the total number of records in the sample_xql_raw dataset, including any with null values, without applying any grouping.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp count() as total_events

Explanation: This query computes the total number of rows in the sample_xql_raw dataset and names the resulting field total_events. Because no field is specified in count(), it includes all rows.

Output:

total_events
10
 

Example 2: Counting non-null values of a specific field across the entire dataset (field specified)

Goal: Count the number of records where the event_description field is not null, across the entire dataset.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp count(event_description) as non_null_descriptions

Explanation: The query calculates the count of records that have a non-null event_description value in the sample_xql_raw dataset.

Output:

non_null_descriptions
10
 

Example 3: Counting non-null values of a specific field, grouped by another field

Goal: Calculate the count of event_id occurrences separately for successful (true) and unsuccessful (false) events.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp count(event_id) as events_by_status by is_successful

Explanation: The query groups records by their is_successful status and then counts the non-null event_id for each group, presenting the counts in the events_by_status field.

Output:

is_successful events_by_status
true 7
false 3
   

Example 4: Counting a value extracted and converted from JSON data

Goal: Calculate the count of records where a numerical value (from code or error_code) is successfully extracted from a JSON field (simple_json_data) and converted to a number.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| alter json_code_number = to_number(coalesce(simple_json_data \-> code, simple_json_data \-> error_code)   
| comp count(json_code_number) as count_of_codes   
| limit 1

Explanation: This query first attempts to extract either the code or error_code from the simple_json_data field using json_extract_scalar(), with coalesce() handling potential nulls. to_number() then converts these to a numerical type. Finally, count() computes the number of these converted numerical values, automatically excluding records where the extraction resulted in a null value.

Output:

count_of_codes
2
 

Example 5: Counting a numerical value from an array element

Goal: Count events where a specific numeric element from an array field (numeric_codes) exists and is not null.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| alter first_numeric_val = arrayindex(numeric_codes, 0)   
| comp count(first_numeric_val) as count_first_numeric_code   
| limit 1

Explanation: This query uses arrayindex() to access the first numeric code from the numeric_codes array for each record. The count() function then calculates the number of records where this extracted value is not null, excluding records where the array is empty (like event ID 104) or the first element is null.

Output:

count_first_numeric_code
9
 

Example 6: Counting with raw data inclusion (addrawdata=true)

Goal: Include the raw events that contribute to each count by using the addrawdata = true option.

XQL Code:

config timeframe = 1d   
| dataset = sample_xql_raw   
| comp count(event_id) by is_successful addrawdata = true as raw_events_for_count   
| limit 3

Explanation: Similar to Variant 3, this query calculates the count of event_id grouped by is_successful. Additionally, addrawdata = true generates a new column named raw_events_for_count, which contains a JSON representation of the raw events that contributed to each computed count.

Output:

is_successful raw_events_for_count
true JSON representation of raw data for 7 events (truncated)
false JSON representation of raw data for 3 events (truncated)