SELECT clause

Use the SELECT clause in Junction Sense queries to choose table columns, apply aggregate functions, and retrieve group key columns.

Select a table column

Select a specific table column using a Table Column expression.

Output column name

The name of the column being selected.

Python DSL

import vitalx.aggregation as va

va.select(va.Sleep.col("efficiency"))

Perform an analysis and select the output value

Perform data analysis with in-built algorithms using value macro expressions:

• Sleep Analysis value macro expressions

Output column name

The name of the value macro, e.g., chronotype for the Chronotype value macro.

Python DSL

import vitalx.aggregation as va

va.select(va.Sleep.chronotype())

Aggregate a table column

Aggregate a Table Column expression with respect to the group_by clause.

The specified aggregate function is applied to each and every group created by the group_by clause. The result set is N aggregated values where N is the number of groups.

Statistical functions

Aggregate function	Python DSL	JSON DSL
Minimum	$EXPR.min()	{ "func": "min", "arg": $EXPR }
Maximum	$EXPR.max()	{ "func": "max", "arg": $EXPR }
Mean	$EXPR.mean()	{ "func": "mean", "arg": $EXPR }
Median	$EXPR.median()	{ "func": "median", "arg": $EXPR }
Standard Deviation	$EXPR.stddev()	{ "func": "stddev", "arg": $EXPR }
Count	$EXPR.count()	{ "func": "count", "arg": $EXPR }
Oldest Value	$EXPR.oldest()	{ "func": "oldest", "arg": $EXPR }
Newest Value	$EXPR.newest()	{ "func": "newest", "arg": $EXPR }

Math functions

Aggregate function	Python DSL	JSON DSL
Sum	$EXPR.sum()	{ "func": "sum", "arg": $EXPR }

Output column name

• the aggregation function name, e.g., sum; or
• if the Split by Source mode is enabled — $FUNCTION_NAME.$SOURCE_COLUMN_NAME, e.g., mean.efficiency.

Python DSL

import vitalx.aggregation as va

va.select(
    va.Sleep.col("stage_asleep_second").newest()
)

Aggregate list-column elements

Resources like Menstrual Cycle expose list-of-struct columns (for example, basal_body_temperature is a list of {date, value} entries). Aggregate their elements with a scalar-output subquery — a self-contained select / from (UNNEST) / optional where block that produces one scalar per outer row.

Output column name

• $FUNC.$COLUMN when the subquery aggregates rows directly (e.g., count.menstrual_flow); or
• $FUNC.$COLUMN.$FIELD when a struct field is extracted (e.g., mean.basal_body_temperature.value); or
• $FUNC.$COLUMN.element when the scalar-list element itself is aggregated (e.g., sum.tags.element).

Python DSL

import vitalx.aggregation as va

va.select(
    va.MenstrualCycle.col("menstrual_flow")
        .unnest_and_select(lambda col: col.count())
)

See List-column aggregation for the full reference, including the where predicate, struct-field extraction, and the reserved element identifier for scalar-element lists.

Select the Index Column

Select the primary datetime index of the table using the Index Column expression.

Output column name

timestamp (constant).

Python DSL

import vitalx.aggregation as va

va.select(va.Sleep.index())

Select the Group Key Columns

Select the Group Key Columns associated with the group_by clause.

You can select one specific group key column by offset, or select all group key columns with a * wildcard.

Output column name

group_key.$OFFSET, where $OFFSET corresponds to the N-th expression of the group_by clause.

Python DSL

import vitalx.aggregation as va

# Select all group key columns
va.select(va.group_key("*"))

# Select the 2nd group key column
va.select(va.group_key(2))