JQXL Syntax Reference

jq pipelines + Excel formula helpers -- the expression language for computed fields in Boxel

See also BFM v3 Syntax Guide -- the document format that uses JQXL for formulas, inline transforms, and table queries.

field paths jq builtins Excel formulas operators variables strings

Language Foundations

Mental Model -- from spreadsheets to JSON
Dot Paths & Navigation -- accessing fields and arrays
Pipes & Transforms -- map, select, sort, group
Constructing Values -- new objects, arrays, strings
Bindings & Variables -- as $name patterns
Control Flow -- if/then/else, try/catch, reduce

Formula Helpers (Excel-Style)

Math & Rounding -- ROUND, ABS, CEILING, FLOOR, MOD, POWER
Statistics -- SUM, AVERAGE, COUNT, STDEV, MIN, MAX
Conditional Aggregation -- SUMIF, COUNTIF, AVERAGEIF + _BY variants
Lookups -- VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE
Text -- TEXTJOIN, LEFT, MID, SUBSTITUTE, DOLLAR, TEXT
Logic & Type -- IF, IFERROR, AND, OR, ISBLANK, ISNUMBER
Date -- DATE, DAY, MONTH, YEAR, YEARFRAC
Financial -- PMT, NPV, IRR, XIRR, FV, PV, RATE
Engineering -- base conversion, bitwise, complex numbers

Using JQXL in Boxel

Computed Fields -- expression(), field types, chaining
jq Builtins Index -- complete list of core jq functions
String Formats -- @base64, @csv, @html, @json, @uri, @sh
Error Codes -- #DIV/0!, #N/A, #VALUE!, #REF!

Core Mental Model

If you know spreadsheets, you know the core idea. The difference: instead of cell references like A1:B10, you work with JSON -- fields, arrays, and objects.

Spreadsheet	JQXL
Cell value	JSON scalar (number, string, boolean)
Range `A1:A10`	JSON array `[1, 2, 3, ...]`
Table with headers	Array of objects `[{name, age}, ...]`
Current row	`.` (the dot -- current JSON value)
`=A1 + B1`	`.price + .tax`
`=SUM(A1:A10)`	`SUM(.items \| map(.amount))`
`=IFERROR(A1, 0)`	`IFERROR(.value; 0)`

Arguments use semicolons ; not commas -- this is jq syntax. ROUND(.x; 2) not ROUND(.x, 2).
Array literals use commas: [1, 2, 3]. If a function call with semicolons is nested inside [...], bind it first: LEFT(.x; 1) as $v | SWITCH([$v, "A", "Alpha"]).

Core Dot Paths & Navigation

Basic Access

ExpressionResult

The entire current value (like "this row")

.name

Value of the name field

.patient.name

Nested field access

.orders[0]

First element of the orders array

.orders[-1]

Last element

.orders[2:5]

Slice: elements at index 2, 3, 4

Safe Access & Fallbacks

ExpressionMeaning

.owner?.email

Optional access -- no error if owner is null

.owner?.email // "none"

Alternative operator -- fallback if null/missing

.items[]

Iterate: emit each element as a separate value

Recursive descent -- all values at every depth

Core Pipes & Transforms

The pipe | chains operations left-to-right. Each step feeds its output to the next.

map, select, sort

Transform every element

.lineItems | map(.qty * .unitPrice)

Filter by condition

.orders | map(select(.status == "paid"))

Sort and reverse

.orders | sort_by(.placedAt) | reverse

Deduplicate

.tags | map(ascii_downcase) | unique | sort

group_by, flatten, add

Group and count

.orders
| group_by(.region)
| map({ region: .[0].region, count: length })

Flatten nested arrays

.departments | map(.employees) | flatten

Sum an array

.scores | add

Operators

Operator	Meaning	Example
`\|`	Pipe	`.items \| map(.x)`
`,`	Both outputs	`.a, .b` emits two values
`+` `-` `*` `/` `%`	Arithmetic	`.price * .qty`
`==` `!=` `<` `<=` `>` `>=`	Comparison	`.age >= 18`
`and` `or` `not`	Logical	`.a and .b`
`//`	Alternative (fallback)	`.x // 0`
`=` `\|=` `+=` `-=`	Update	`.count += 1`

Core Constructing Values

Objects

ExpressionResult

{id, status, total}

Shorthand -- pulls fields from current value

{patient: .patientName, ward: .ward}

Renamed fields

Arrays

ExpressionResult

[.name, .ward, .diagnosis]

Array from specific fields

[.items[] | .name]

Collect iterated values into array

String Interpolation

"Patient: \(.firstName) \(.lastName)"

Embed computed values in strings

Core Bindings & Variables

Use as $name to capture intermediate values. Avoids repeating complex sub-expressions.

Bind and reuse

(.items | map(.qty * .price) | add) as $subtotal
| {
    subtotal: $subtotal,
    tax: ROUND($subtotal * 0.0825; 2),
    total: ROUND($subtotal * 1.0825; 2)
  }

Tip: When in doubt, bind it. $subtotal is clearer than repeating the pipeline three times, and safer than threading it through pipes that might break when edited later.

Core Control Flow

Pattern	Syntax
Conditional	`if .x > 0 then "positive" elif .x == 0 then "zero" else "negative" end`
Try/catch	`try (.x / .y) catch "division error"`
Reduce	`reduce .items[] as $i (0; . + $i.amount)`
Foreach	`foreach .items[] as $i (0; . + $i.amount)`
Define function	`def double: . * 2; .values \| map(double)`
Label/break	`label $out \| foreach .[] as $x (0; .+$x; if .>100 then ., break $out else . end)`

Formula Math & Rounding

Function	Description	Example
`ROUND(n; d)`	Round to d decimal places	`ROUND(3.14159; 2)` → `3.14`
`ROUNDUP(n; d)`	Round away from zero	`ROUNDUP(3.141; 2)` → `3.15`
`ROUNDDOWN(n; d)`	Round toward zero	`ROUNDDOWN(3.149; 2)` → `3.14`
`ABS(n)`	Absolute value	`ABS(-42)` → `42`
`CEILING(n; s)`	Round up to significance (alias: `CEILING_MATH`)	`CEILING(2.3; 1)` → `3`
`FLOOR(n; s)`	Round down to significance (alias: `FLOOR_MATH`)	`FLOOR(2.7; 1)` → `2`
`INT(n)`	Floor to integer	`INT(3.9)` → `3`
`MOD(a; b)`	Modulo (always non-negative)	`MOD(10; 3)` → `1`
`POWER(n; p)`	Exponentiation	`POWER(2; 10)` → `1024`
`SQRT(n)`	Square root	`SQRT(144)` → `12`
`PRODUCT(arr)`	Product of all numbers	`PRODUCT([2, 3, 4])` → `24`
`PI`	Constant 3.14159...	`PI` → `3.14159...`
`SIGN(n)`	Returns -1, 0, or 1	`SIGN(-42)` → `-1`
`EVEN(n)`	Round up to nearest even	`EVEN(3)` → `4`
`ODD(n)`	Round up to nearest odd	`ODD(4)` → `5`
`GCD(arr)`	Greatest common divisor	`GCD([12, 8, 20])` → `4`
`LCM(arr)`	Least common multiple	`LCM([4, 6, 10])` → `60`
`FACT(n)`	Factorial	`FACT(5)` → `120`
`FACTDOUBLE(n)`	Double factorial	`FACTDOUBLE(7)` → `105`
`COMBIN(n; k)`	Combinations	`COMBIN(10; 3)` → `120`
`COMBINA(n; k)`	Combinations with repetition	`COMBINA(4; 2)` → `10`
`PERMUT(n; k)`	Permutations	`PERMUT(10; 3)` → `720`
`MROUND(n; m)`	Round to nearest multiple	`MROUND(7.5; 3)` → `9`
`QUOTIENT(n; d)`	Integer division	`QUOTIENT(7; 2)` → `3`
`DEGREES(rad)`	Radians to degrees	`DEGREES(PI)` → `180`
`RADIANS(deg)`	Degrees to radians	`RADIANS(180)` → `3.14159...`
`RAND`	Random number 0--1	`RAND` → `0.7291...`
`RANDBETWEEN(lo; hi)`	Random integer in range	`RANDBETWEEN(1; 100)`
`SUMPRODUCT(arrays)`	Sum of element-wise products	`SUMPRODUCT([[1,2],[3,4]])` → `11`
`SUMSQ(arr)`	Sum of squares	`SUMSQ([3, 4])` → `25`

Formula Statistics

Function	Description	Example
`SUM(arr)`	Sum of numbers (skips nulls)	`SUM([10, 20, 30])` → `60`
`AVERAGE(arr)`	Mean (skips nulls)	`AVERAGE([10, 20, 30])` → `20`
`COUNT(arr)`	Count of finite numbers	`COUNT([1, "a", 3])` → `2`
`COUNTA(arr)`	Count of non-blank values	`COUNTA([1, "a", null])` → `2`
`MAX(arr)`	Maximum	`MAX([3, 1, 4])` → `4`
`MIN(arr)`	Minimum	`MIN([3, 1, 4])` → `1`
`STDEV(arr)`	Sample standard deviation (alias: `STDEV_S`)	`STDEV([2, 4, 4, 4, 5, 5, 7, 9])`
`STDEV_P(arr)`	Population standard deviation	`STDEV_P([2, 4, 4, 4, 5, 5, 7, 9])`
`MEDIAN(arr)`	Middle value	`MEDIAN([1, 3, 5, 7])` → `4`
`VAR(arr)`	Sample variance (alias: `VAR_S`)	`VAR([2, 4, 4, 4, 5, 5, 7, 9])`
`VAR_P(arr)`	Population variance	`VAR_P([2, 4, 4, 4, 5, 5, 7, 9])`
`LARGE(arr; k)`	Kth largest	`LARGE([3, 1, 4, 1, 5]; 2)` → `4`
`SMALL(arr; k)`	Kth smallest	`SMALL([3, 1, 4, 1, 5]; 2)` → `1`
`COUNTBLANK(arr)`	Count of empty/null values	`COUNTBLANK([1, null, "", 3])` → `2`
`RANK_EQ(n; arr)`	Rank (descending)	`RANK_EQ(4; [5, 4, 3, 2])` → `2`
`RANK_AVG(n; arr)`	Rank with average for ties	`RANK_AVG(4; [5, 4, 4, 2])` → `2.5`
`PERCENTILE_INC(arr; k)`	Percentile (inclusive)	`PERCENTILE_INC([1,2,3,4]; 0.75)` → `3.25`
`QUARTILE_INC(arr; q)`	Quartile (0--4)	`QUARTILE_INC([1,2,3,4]; 1)` → `1.75`
`CORREL(x; y)`	Correlation coefficient	`CORREL([1,2,3]; [2,4,6])` → `1`
`SLOPE(y; x)`	Linear regression slope	`SLOPE([2,4,6]; [1,2,3])` → `2`
`INTERCEPT(y; x)`	Linear regression intercept	`INTERCEPT([2,4,6]; [1,2,3])` → `0`
`FORECAST(x; y; knownX)`	Linear prediction	`FORECAST(4; [2,4,6]; [1,2,3])` → `8`
`GEOMEAN(arr)`	Geometric mean	`GEOMEAN([2, 8])` → `4`
`HARMEAN(arr)`	Harmonic mean	`HARMEAN([2, 8])` → `3.2`
`TRIMMEAN(arr; pct)`	Mean excluding outliers	`TRIMMEAN([1,2,3,4,100]; 0.4)`
`AVEDEV(arr)`	Average absolute deviation	`AVEDEV([2, 4, 8, 16])`
`DEVSQ(arr)`	Sum of squared deviations	`DEVSQ([2, 4, 8, 16])`
`SKEW(arr)`	Distribution skewness	`SKEW([1, 2, 3, 4, 100])` → positive
`KURT(arr)`	Distribution kurtosis	`KURT([1, 2, 3, 4, 100])` → heavy-tailed
`MAXIFS([vals; range; crit])`	Conditional max	`MAXIFS([[5,3,8]; [1,2,1]; 1])` → `8`
`MINIFS([vals; range; crit])`	Conditional min	`MINIFS([[5,3,8]; [1,2,1]; 1])` → `5`
`PERCENTILE_EXC(arr; k)`	Percentile (exclusive)	`PERCENTILE_EXC([1,2,3,4,5]; 0.4)`
`QUARTILE_EXC(arr; q)`	Quartile exclusive (1--3)	`QUARTILE_EXC([1,2,3,4,5]; 1)`
`PERCENTRANK_INC(arr; x)`	Percentile rank (inclusive)	`PERCENTRANK_INC([1,2,3,4]; 3)` → `0.667`
`PERCENTRANK_EXC(arr; x)`	Percentile rank (exclusive)	`PERCENTRANK_EXC([1,2,3,4]; 3)` → `0.6`
`PEARSON(x; y)`	Pearson correlation (alias for CORREL)	`PEARSON([1,2,3]; [2,4,6])` → `1`

SUM and AVERAGE automatically skip nulls and non-numeric values, just like their spreadsheet counterparts.

Formula Conditional Aggregation

Two flavors: range-based (classic Excel) and _BY variants for arrays of objects.

Range-Based (Classic)

Function	Description
`SUMIF(range; criteria)`	Sum where criteria matches
`SUMIF(range; criteria; sumRange)`	Sum from a different range
`COUNTIF(range; criteria)`	Count where criteria matches
`AVERAGEIF(range; criteria)`	Average where criteria matches

Row-Object Variants (_BY)

Function	Example
`SUMIF_BY(rows; valueKey; criteriaKey; criteria)`	`SUMIF_BY(.; "amount"; "region"; "East")`
`SUMIFS_BY(rows; valueKey; criteriaObj)`	`SUMIFS_BY(.; "amount"; {region: "East", team: "A"})`
`COUNTIF_BY(rows; criteriaKey; criteria)`	`COUNTIF_BY(.; "status"; "active")`
`COUNTIFS_BY(rows; criteriaObj)`	`COUNTIFS_BY(.; {region: "East", team: "A"})`
`AVERAGEIF_BY(rows; valueKey; criteriaKey; criteria)`	`AVERAGEIF_BY(.; "score"; "grade"; "A")`
`AVERAGEIFS_BY(rows; valueKey; criteriaObj)`	`AVERAGEIFS_BY(.; "score"; {grade: "A"})`

Criteria strings support prefixes: =, <>, >, >=, <, <=. Wildcards: * (any chars), ? (single char). Escape with ~.

Formula Lookups

Function	Description
`VLOOKUP(val; table; colIdx)`	Vertical lookup -- search first column, return nth column
`VLOOKUP(val; table; colIdx; rangeLookup)`	Set `rangeLookup` to `false` for exact match
`VLOOKUP_BY(rows; lookupKey; val; resultKey)`	Row-object VLOOKUP
`HLOOKUP(val; table; rowIdx)`	Horizontal lookup
`LOOKUP(val; lookupArr; resultArr)`	Simple lookup with two arrays
`LOOKUP_BY(rows; lookupKey; val; resultKey)`	Row-object LOOKUP
`MATCH(val; arr; matchType)`	Position of match (1-based). matchType: 1, 0, or -1
`INDEX(arr; row)`	Value at position
`INDEX(arr; row; col)`	Value at row and column
`CHOOSE(idx; options)`	Select from list by 1-based index
`COL(rows; key)`	Extract column values from array of objects

VLOOKUP_BY example

VLOOKUP_BY(.; "label"; "ventilator"; "amount")

INDEX + MATCH pattern

INDEX(.names; MATCH("target"; .ids; 0))

Formula Text

Function	Description	Example
`TEXTJOIN(delim; skipEmpty; arr)`	Join with delimiter	`TEXTJOIN(", "; true; .names)`
`CONCAT(arr)`	Concatenate array values (alias: `CONCATENATE`)	`CONCAT(["a", "b"])` → `"ab"`
`LEFT(text; n)`	First n characters	`LEFT("Hello"; 3)` → `"Hel"`
`RIGHT(text; n)`	Last n characters	`RIGHT("Hello"; 2)` → `"lo"`
`MID(text; start; n)`	Substring (1-based)	`MID("Hello"; 2; 3)` → `"ell"`
`LEN(text)`	String length	`LEN("Hello")` → `5`
`UPPER(text)`	Uppercase	`UPPER("hi")` → `"HI"`
`LOWER(text)`	Lowercase	`LOWER("HI")` → `"hi"`
`PROPER(text)`	Title Case	`PROPER("hello world")` → `"Hello World"`
`TRIM(text)`	Trim + collapse spaces	`TRIM(" hi there ")` → `"hi there"`
`SUBSTITUTE(text; old; new)`	Replace all occurrences	`SUBSTITUTE("aaa"; "a"; "b")` → `"bbb"`
`SUBSTITUTE(text; old; new; n)`	Replace nth occurrence	`SUBSTITUTE("aaa"; "a"; "b"; 2)` → `"aba"`
`REPLACE(text; pos; len; new)`	Replace by position	`REPLACE("Hello"; 1; 2; "Ya")` → `"Yallo"`
`REPT(text; n)`	Repeat	`REPT("ab"; 3)` → `"ababab"`
`FIND(find; within)`	Case-sensitive position (1-based)	`FIND("lo"; "Hello")` → `4`
`SEARCH(find; within)`	Case-insensitive position	`SEARCH("LO"; "Hello")` → `4`
`EXACT(a; b)`	Case-sensitive equality	`EXACT("Hi"; "hi")` → `false`
`CHAR(n)`	Code point to character (alias: `UNICHAR`)	`CHAR(65)` → `"A"`
`CODE(text)`	First char to code point (alias: `UNICODE`)	`CODE("A")` → `65`
`CLEAN(text)`	Remove control chars	Strips 0x00-0x1F
`VALUE(text)`	Parse string as number	`VALUE("42")` → `42`
`NUMBERVALUE(text; dec; grp)`	Locale-aware parsing	`NUMBERVALUE("1.234,56"; ","; ".")`

Formatting Helpers

Function	Description	Example
`TEXT(val; fmt)`	Format number as string	`TEXT(.date; "yyyy-mm-dd")`
`DOLLAR(n; d)`	Format as currency	`DOLLAR(1234.5; 2)` → `"$1,234.50"`
`FIXED(n; d; noCommas)`	Fixed decimal string	`FIXED(1234.5; 2; false)` → `"1,234.50"`

Formula Logic & Type Checking

Conditionals

Function	Description	Example
`IF(test; then; else)`	Excel-style conditional	`IF(.score >= 90; "A"; "B")`
`IFERROR(val; fallback)`	Catch any error	`IFERROR(.x / .y; 0)`
`IFNA(val; fallback)`	Catch only #N/A	`IFNA(VLOOKUP(...); "missing")`
`IFS(c1; v1; c2; v2; ...)`	Multiple conditions (2--4 pairs)	`IFS(.x > 90; "A"; .x > 80; "B"; true; "C")`
`SWITCH([expr, v1, r1, ...])`	Value-based dispatch (commas inside array)	`SWITCH([.status, "A", "Active", "I", "Inactive", "Unknown"])`

Boolean Functions

Function	Description
`TRUE`	Literal `true`
`FALSE`	Literal `false`
`NA`	Produce #N/A error (use as sentinel)
`NOT(val)`	Boolean NOT
`AND(arr)`	All truthy (skips nulls/strings)
`OR(arr)`	Any truthy (skips nulls/strings)
`XOR(arr)`	Odd count of truthy values

Type Checking & Error Detection

Function	Description
`ISBLANK(val)`	True if null/undefined
`ISNUMBER(val)`	True if finite number
`ISTEXT(val)`	True if string
`ISERROR(val)`	True if any error
`ISNA(val)`	True if #N/A error
`ISERR(val)`	True if error but not #N/A
`TYPE(val)`	Returns 1 (num), 2 (str), 4 (bool), 16 (error), 64 (array)
`N(val)`	Convert to number (1/0 for bool)
`T(val)`	Return string or `""`
`ERROR_TYPE(val)`	Error type code (1--8)
`ISEVEN(val)`	True if even integer
`ISODD(val)`	True if odd integer
`ISLOGICAL(val)`	True if boolean
`ISNONTEXT(val)`	True if not a string

Formula Date

Formula Helpers

Function	Description
`DATE(year; month; day)`	Build Excel date serial number
`DAY(date)`	Day of month (1--31)
`MONTH(date)`	Month (1--12)
`YEAR(date)`	Year
`YEARFRAC(start; end; basis)`	Fraction of year between dates (basis 0--4)
`DAYS(end; start)`	Days between two date serials
`TODAY`	Current date as Excel serial number
`HOUR(serial)`	Hour component (0--23)
`MINUTE(serial)`	Minute component (0--59)
`SECOND(serial)`	Second component (0--59)
`WEEKDAY(serial; type)`	Day of week (type 1: Sun=1..Sat=7)
`ISOWEEKNUM(serial)`	ISO week number
`EDATE(start; months)`	Date offset by N months
`EOMONTH(start; months)`	End of month, offset by N months
`DATEDIF(start; end; unit)`	Difference in "Y", "M", "D", "MD", "YM", "YD"
`DATEVALUE(text)`	Parse date string to serial number
`DAYS360(start; end; method)`	Days on 360-day year (US/European)
`WEEKNUM(serial; type)`	Week number (type 1: Sun start, 2: Mon start)
`NETWORKDAYS(start; end; holidays)`	Business days between dates
`WORKDAY(start; days; holidays)`	Date offset by N business days
`TIME(hour; min; sec)`	Time as fraction of day
`TIMEVALUE(text)`	Parse time string ("14:30") to fraction

jq Date Functions

Function	Description
`now`	Current Unix timestamp
`strptime(fmt)`	Parse date string
`strftime(fmt)`	Format timestamp
`mktime`	Broken-down time to Unix timestamp
`gmtime`	Unix timestamp to UTC time array
`todate`	Timestamp to ISO-8601 string
`fromdate`	ISO-8601 string to timestamp

Date difference in days

((.endDate | strptime("%Y-%m-%d") | mktime)
 - (.startDate | strptime("%Y-%m-%d") | mktime)) / 86400

Formula Financial

Time Value of Money

Function	Description
`PMT(rate; nper; pv; fv; type)`	Payment per period
`IPMT(rate; per; nper; pv; fv; type)`	Interest portion of payment
`PPMT(rate; per; nper; pv; fv; type)`	Principal portion of payment
`FV(rate; nper; pmt; pv; type)`	Future value
`PV(rate; nper; pmt; fv; type)`	Present value
`NPER(rate; pmt; pv; fv; type)`	Number of periods
`RATE(nper; pmt; pv; fv; type; guess)`	Interest rate per period
`FVSCHEDULE(principal; schedule)`	FV with variable rates

Investment Analysis

Function	Description
`NPV(rate; values)`	Net present value
`NPV_BY(rate; rows; valueKey)`	NPV from row objects
`IRR(values; guess)`	Internal rate of return
`IRR_BY(rows; valueKey; guess)`	IRR from row objects
`MIRR(values; finRate; reinRate)`	Modified IRR
`XNPV(rate; values; dates)`	NPV for irregular cash flows
`XNPV_BY(rate; rows; valKey; dateKey)`	XNPV from row objects
`XIRR(values; dates; guess)`	IRR for irregular cash flows
`XIRR_BY(rows; valKey; dateKey; guess)`	XIRR from row objects

Other Financial

Function	Description
`CUMIPMT(rate; nper; pv; start; end; type)`	Cumulative interest between periods
`CUMPRINC(rate; nper; pv; start; end; type)`	Cumulative principal between periods
`EFFECT(nominal; npery)`	Effective annual rate
`NOMINAL(effective; npery)`	Nominal annual rate
`SLN(cost; salvage; life)`	Straight-line depreciation
`SYD(cost; salvage; life; per)`	Sum-of-years depreciation
`ACCRINT(issue; first; settle; rate; par; freq)`	Accrued interest
`DB(cost; salvage; life; period; month)`	Declining balance depreciation
`DDB(cost; salvage; life; period; factor)`	Double declining balance
`ISPMT(rate; per; nper; pv)`	Interest for straight-line payments
`PDURATION(rate; pv; fv)`	Periods to reach target value
`RRI(nper; pv; fv)`	Required rate of return
`DOLLARDE(frac; denom)`	Fractional dollar to decimal
`DOLLARFR(dec; denom)`	Decimal dollar to fractional
`DISC(settle; maturity; pr; redemption; basis)`	Discount rate of a security
`PRICEDISC(settle; maturity; disc; redemption; basis)`	Price of discounted security
`COUPDAYS(settle; maturity; freq; basis)`	Days in coupon period
`TBILLEQ(settle; maturity; discount)`	T-bill bond-equivalent yield
`TBILLPRICE(settle; maturity; discount)`	T-bill price per $100
`TBILLYIELD(settle; maturity; price)`	T-bill yield

Monthly car payment

PMT(0.065 / 12; 60; 25000; 0; 0)

Project NPV

NPV(0.08; [-40000, 8000, 9200, 10000, 12000])

Formula Engineering

Base Conversion

Function	Description
`BIN2DEC` / `DEC2BIN`	Binary ↔ Decimal
`BIN2HEX` / `HEX2BIN`	Binary ↔ Hex
`BIN2OCT` / `OCT2BIN`	Binary ↔ Octal
`DEC2HEX` / `HEX2DEC`	Decimal ↔ Hex
`DEC2OCT` / `OCT2DEC`	Decimal ↔ Octal
`OCT2HEX` / `HEX2OCT`	Octal ↔ Hex
`BASE(n; radix; minLen)`	Arbitrary base conversion
`DECIMAL(text; radix)`	Parse from arbitrary base

Bitwise Operations

Function	Description
`BITAND(a; b)`	Bitwise AND
`BITOR(a; b)`	Bitwise OR
`BITXOR(a; b)`	Bitwise XOR
`BITLSHIFT(n; shift)`	Left shift
`BITRSHIFT(n; shift)`	Right shift

Complex Numbers

Function	Description
`COMPLEX(real; imag; suffix)`	Construct complex number string
`IMABS(val)`	Modulus
`IMREAL(val)`	Real part
`IMAGINARY(val)`	Imaginary part
`IMCONJUGATE(val)`	Complex conjugate
`IMSUB(a; b)`	Complex subtraction
`IMSUM(values)`	Complex sum
`DELTA(a; b)`	1 if equal, 0 otherwise
`GESTEP(n; step)`	1 if ≥ step, 0 otherwise
`IMCOS(z)`	Complex cosine
`IMSIN(z)`	Complex sine
`IMTAN(z)`	Complex tangent
`IMEXP(z)`	Complex exponential
`IMLN(z)`	Complex natural log
`IMLOG10(z)`	Complex log base 10
`IMLOG2(z)`	Complex log base 2
`IMDIV(a; b)`	Complex division
`IMPRODUCT(values)`	Complex product
`IMPOWER(z; n)`	Complex power
`IMSQRT(z)`	Complex square root
`IMARGUMENT(z)`	Angle (argument) of complex number
`ERF(lower; upper)`	Error function (Gauss)
`ERFC(x)`	Complementary error function
`CONVERT(n; from; to)`	Unit conversion (120+ units, SI prefixes)

CONVERT examples

CONVERT(1; "mi"; "km")       // 1.609344
CONVERT(100; "C"; "F")       // 212
CONVERT(1; "gal"; "L")      // 3.785...
CONVERT(1; "kg"; "lbm")    // 2.204...
CONVERT(1024; "byte"; "kibyte") // 1 (binary prefix)

Boxel Computed Fields

In Boxel, JQXL expressions live inside computeVia on a field definition. The field type acts as the output contract.

Basic pattern

import { expression } from './jqxl';

@field total = contains(NumberField, {
  computeVia: expression('ROUND(.subtotal * (1 + .taxRate); 2)'),
});

Structured result

@field summary = contains(SummaryField, {
  computeVia: expression(`
    (.items | map(.qty * .price) | add) as $sub
    | {
        subtotal: $sub,
        tax: ROUND($sub * 0.0825; 2),
        total: ROUND($sub * 1.0825; 2)
      }
  `, { as: SummaryField }),
});

Array result

@field alerts = containsMany(AlertField, {
  computeVia: expression(`
    .issues
    | map(select(.severity == "high"))
    | map({ label: .title, owner: (.owner.name // "unassigned") })
  `, { as: AlertField }),
});

Field Type → Expression Return Type

Field Declaration	Expression Must Return
`contains(NumberField)`	A number
`contains(StringField)`	A string
`contains(BooleanField)`	A boolean
`contains(SomeFieldDef)`	An object matching the FieldDef shape
`containsMany(NumberField)`	An array of numbers
`containsMany(SomeFieldDef)`	An array of objects

Chaining Computed Fields

One field references another

@field subtotal = contains(NumberField, {
  computeVia: expression('.lineItems | map(.qty * .unitPrice) | add'),
});

@field total = contains(NumberField, {
  computeVia: expression('ROUND(.subtotal * 1.0825; 2)'),
});

Computed fields can reference other computed fields on the same card. Each field is one step -- explicit, testable, and easy to trace.

Reading Across Relationships

ExpressionWhat it does

.attendingClinician.name

Read a field from a linked card

.consultTeam | map(.name)

Map over a linksToMany relationship

.patient.firstName + " " + .patient.lastName

Concatenate across a link

Boxel jq Builtins Index

Complete index of core jq functions available in JQXL.

Array / Object

Function	Description
`length`	Length of string/array/object
`keys` / `keys_unsorted`	Sorted / unsorted keys
`has(key)`	Test if key/index exists
`contains(v)`	Deep containment check
`in(obj)`	Test if input is key in obj
`inside(v)`	Test if input is contained in v
`map(f)`	Apply f to each element
`map_values(f)`	Apply f to all values
`select(f)`	Keep elements where f is truthy
`del(f)`	Delete paths
`add`	Sum/fold array
`sort` / `sort_by(f)`	Sort array
`reverse`	Reverse array
`unique` / `unique_by(f)`	Deduplicate
`group_by(f)`	Group by key
`min_by(f)` / `max_by(f)`	Extremes by key
`flatten` / `flatten(n)`	Flatten to depth
`join(sep)`	Join array to string
`to_entries`	Object to [{key, value}]
`from_entries`	[{key, value}] to object
`with_entries(f)`	Transform entries
`transpose`	Matrix transpose
`indices(v)`	All indices of v
`index(v)` / `rindex(v)`	First / last index
`range(n)` / `range(a;b)`	Number range
`walk(f)`	Apply f to all nodes bottom-up
`recurse(f)`	Recursive descent
`paths` / `leaf_paths`	All / leaf paths
`getpath(p)` / `setpath(p; v)`	Path access
`pick(pathexps)`	Pick paths from input
`ROWS(arr)` / `COLUMNS(arr)`	Row/column count

String

Function	Description
`ascii_downcase` / `ascii_upcase`	Case conversion
`ltrimstr(s)` / `rtrimstr(s)`	Remove prefix / suffix
`trim` / `ltrim` / `rtrim`	Trim whitespace
`startswith(s)` / `endswith(s)`	Test prefix / suffix
`split(s)`	Split on string
`test(re)` / `match(re)`	Regex test / match
`capture(re)`	Named capture groups
`scan(re)`	All regex matches
`sub(re; s)` / `gsub(re; s)`	Regex substitution
`tostring` / `tonumber`	Type conversion
`tojson` / `fromjson`	JSON encode/decode
`explode` / `implode`	String ↔ codepoints

Type Selectors

Function	Description
`type`	Return type name as string
`arrays` / `objects` / `strings` / `numbers` / `booleans` / `nulls`	Select by type
`values` / `scalars`	Non-null / scalar values
`iterables`	Arrays or objects
`isnan` / `isinfinite` / `isnormal` / `isfinite`	Number predicates
`nan` / `infinite`	IEEE constants

Iteration & Flow

Function	Description
`first` / `first(g)`	First element / output
`last` / `last(g)`	Last element / output
`nth(n; g)`	Nth output
`limit(n; g)`	First n outputs
`skip(n; g)`	Skip first n outputs
`isempty(g)`	True if generator is empty
`any` / `any(f)` / `any(g; f)`	Existential test
`all` / `all(f)` / `all(g; f)`	Universal test
`while(cond; update)`	Emit while condition holds
`until(cond; next)`	Apply until condition holds
`repeat(f)`	Repeat forever
`combinations`	Cartesian product
`empty`	Produce no output

Math (jq native)

Category	Functions
Trig	`sin` `cos` `tan` `asin` `acos` `atan` `atan2(y)`
Hyperbolic	`sinh` `cosh` `tanh` `asinh` `acosh` `atanh`
Exp/Log	`exp` `exp2` `exp10` `log` `log2` `log10` `pow(y)` `sqrt` `cbrt`
Rounding	`floor` `ceil` `round` `trunc` `fabs`
Other	`hypot(y)` `fmax(y)` `fmin(y)`

Boxel String Formats

Used as @fmt "..." or format("fmt") for string interpolation.

Format	Description	Example
`@text`	Convert to string (default)	`42 \| @text` → `"42"`
`@json`	JSON-encode	`{a:1} \| @json` → `'{"a":1}'`
`@html`	HTML-escape	`"<b>" \| @html` → `"<b>"`
`@uri`	Percent-encode	`"a b" \| @uri` → `"a%20b"`
`@urid`	Percent-decode	`"a%20b" \| @urid` → `"a b"`
`@csv`	CSV row	`["a","b"] \| @csv` → `"\"a\",\"b\""`
`@tsv`	TSV row	`["a","b"] \| @tsv` → `"a\tb"`
`@sh`	Shell-escape	`"it's" \| @sh` → `"'it'\\''s'"`
`@base64`	Base64 encode	`"hello" \| @base64`
`@base64d`	Base64 decode	`"aGVsbG8=" \| @base64d`

Boxel Error Codes

JQXL uses Excel-compatible error codes. Catch them with IFERROR, IFNA, or try/catch.

Error	Code	Meaning
`#NULL!`	1	Null reference
`#DIV/0!`	2	Division by zero
`#VALUE!`	3	Wrong value type
`#REF!`	4	Invalid reference
`#NAME?`	5	Unknown function name
`#NUM!`	6	Numeric error (overflow, domain)
`#N/A`	7	Value not available
`#GETTING_DATA`	8	Data still loading

Handling errors

IFERROR(.revenue / .headcount; 0)

try (.x | tonumber) catch "not a number"

IF(ISERROR(.lookup); "fallback"; .lookup)

Boxel Excel Function Coverage

JQXL implements 230+ functions from the Excel/Google Sheets function set, plus 16 JQXL-only extensions (the _BY row-object variants). This section maps coverage against the formulajs library.

Available via jq (use lowercase)

These Excel functions are not implemented as uppercase helpers because jq already provides them natively. Use the lowercase jq version.

Excel	jq equivalent	Category
`SIN` `COS` `TAN`	`sin` `cos` `tan`	Trig
`ASIN` `ACOS` `ATAN` `ATAN2`	`asin` `acos` `atan` `atan2`	Trig
`SINH` `COSH` `TANH`	`sinh` `cosh` `tanh`	Hyperbolic
`ASINH` `ACOSH` `ATANH`	`asinh` `acosh` `atanh`	Hyperbolic
`EXP` `LOG` `LOG10`	`exp` `log` `log10`	Exp/Log
`LN`	`log` (jq's `log` is natural log)	Exp/Log
`TRUNC`	`trunc`	Rounding
`SORT`	`sort` / `sort_by(f)`	Array
`UNIQUE`	`unique` / `unique_by(f)`	Array
`TRANSPOSE`	`transpose`	Array
`NOW`	`now` (Unix timestamp)	Date

Range-based multi-criteria functions (SUMIFS, COUNTIFS, AVERAGEIFS) are not implemented as uppercase helpers because JQXL's _BY variants (SUMIFS_BY, COUNTIFS_BY, AVERAGEIFS_BY) are more natural for JSON data. Use those instead.

Won't Add

Functions that don't apply to JQXL's JSON context, require a spreadsheet grid model, or are too niche for document formulas.

Category	Functions	Reason
Database	`DAVERAGE` `DCOUNT` `DCOUNTA` `DGET` `DMAX` `DMIN` `DPRODUCT` `DSTDEV` `DSTDEVP` `DSUM` `DVAR` `DVARP`	Excel database functions assume a flat cell range with criteria ranges. JQXL uses `map`/`select`/`_BY` variants instead -- more powerful on JSON.
Grid reference	`COLUMN` `ROW` `SUBTOTAL` `AGGREGATE`	Require a cell grid model. No equivalent concept in JSON expressions.
Matrix	`MMULT` `MUNIT`	Matrix multiplication and identity. Use jq array operations or dedicated math libraries.
Distributions	`BETA.DIST` `BINOM.DIST` `CHISQ.DIST` `F.DIST` `GAMMA.DIST` `NORM.DIST` `POISSON.DIST` `T.DIST` `WEIBULL.DIST` and all their `.INV`, `.RT`, `.TEST` variants	53 statistical distribution functions. Too specialized for document formulas. Use a statistics library for serious analysis.
Regression	`LINEST` `LOGEST` `GROWTH` `TREND`	Array-returning regression functions. Complex output shapes don't map well to single computed fields.
Bessel	`BESSELI` `BESSELJ` `BESSELK` `BESSELY`	Highly specialized engineering functions.
Formatting	`ROMAN` `ARABIC`	Roman numeral conversion. Extremely niche.
Other niche	`SERIESSUM` `MULTINOMIAL` `SQRTPI` `SUMX2MY2` `SUMX2PY2` `SUMXMY2`	Specialized math. Expressible via jq pipelines.

Coverage Summary

Status	Count	Notes
Implemented	230+	All targeted Excel-compatible formula functions
JQXL-only extensions	16	`_BY` row-object variants, `COL`, `ERROR_TYPE`
Available via jq	23	Trig, exp/log, sort, unique, transpose, now (use lowercase)
Won't add	~80	Database, distributions, grid-dependent, Bessel, regression arrays