Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Built-in Functions

mq-db-specific

FunctionDescription
under(pre, post, anc_pre, anc_post)O(1) interval ancestor check — see Index Layers
mq(program, content)Run an mq program against Markdown content
json_extract(json, path)Extract a value from a JSON string
 
-- Hierarchy query: everything nested under a heading
SELECT b.block_type, b.content
FROM blocks b
WHERE under(b.pre, b.post,
  (SELECT pre FROM blocks WHERE block_type = 'heading' AND content = 'Architecture'),
  (SELECT post FROM blocks WHERE block_type = 'heading' AND content = 'Architecture'));

-- Run an mq program inline against block content
SELECT mq('.h1 | to_text', content) AS title
FROM blocks WHERE block_type = 'code' AND lang = 'markdown';

String

FunctionDescription
lower / upperCase conversion
length / len / char_length / character_lengthCharacter count
trim / ltrim / rtrimStrip whitespace, or the given characters
concat / concat_wsJoin strings (with optional separator)
replaceReplace all occurrences of a substring
substring / substrExtract a substring (1-based, FROM/FOR or comma form)
position / instrFind the 1-based index of a substring (0 if absent)
left / rightFirst/last n characters
lpad / rpadPad to a fixed length
reverseReverse a string
repeatRepeat a string n times
initcapCapitalize each word
ascii / chrChar ↔ code point
split_partExtract the nth delimiter-separated field

Numeric

FunctionDescription
absAbsolute value
round / trunc / truncateRound / truncate, with optional decimal scale
ceil / ceiling / floorRound up / down
modRemainder
power / pow / sqrtExponentiation / square root
exp / lne^x / natural log
log / log10 / log2Logarithm (1-arg = base 10, 2-arg = custom base)
sign-1 / 0 / 1
piπ
greatest / leastMax / min across arguments (ignoring NULL)

Date/Time

FunctionDescription
now / current_timestampCurrent UTC date and time
current_dateCurrent UTC date
current_timeCurrent UTC time

Null handling & control flow

FunctionDescription
coalesce / ifnullFirst non-NULL argument
nullifNULL if the two arguments are equal
CASE WHEN … THEN … ELSE … ENDConditional expressions
typeofRuntime type of a value

Aggregates

Usable with GROUP BY:

FunctionDescription
count(*) / count(DISTINCT col)Row / distinct-value count
min / max / sum / avgStandard aggregates
group_concat / string_agg(expr[, sep])Concatenate group values (default separator ,)