Extract a value from a JSON string. path is a key, a dotted path or an index: owner, $.a.b, tags[0]. Scalars keep their type, objects and arrays come back as JSON text, and a missing path or invalid JSON gives NULL
cosine_similarity(a, b)
Cosine similarity of two vectors given as JSON arrays of numbers, e.g. '[0.1,0.2]'. NULL for invalid JSON, non-numbers, an empty array, mismatched lengths or a zero vector
dot_product(a, b)
Dot product, same input rules
l2_distance(a, b)
Euclidean distance, same input rules
match(content, query)
Full-text search: true iff every tokenized term in query appears in content; index-accelerated when content is a bare column reference and query is a string literal. See Full-Text Search
score(content, query)
Simple term-frequency relevance score for query against content (no IDF, see Storage Format)
bm25(content, query)
Okapi BM25 relevance score, IDF-weighted across the whole store (k1=1.2, b=0.75)
-- 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';