ClickHouse

ClickHouse SQL uses unquoted column names, match() for regex, and LIKE for wildcards.

Basic Examples

FlyQL	Generated SQL
message = 'hello'	message = 'hello'
message != 'hello'	message != 'hello'
count > 10	count > 10
count <= 100	count <= 100
message ~ 'error.*'	match(message, 'error.*')
message !~ 'test.*'	NOT match(message, 'test.*')
host = 'prod'	host = 'prod'

Boolean Operators

FlyQL	Generated SQL
a = '1' and b > 10	a = '1' AND b > 10
a = '1' or b = '2'	a = '1' OR b = '2'
not a = '1'	NOT (a = '1')

List Membership

FlyQL	Generated SQL
status in [200, 201]	status IN (200, 201)
env not in ['dev', 'test']	env NOT IN ('dev', 'test')
status in []	0
status not in []	1

Truthy Checks

A bare column reference (active, not active) generates type-aware SQL — there is no single “is truthy” expression, because what counts as falsy differs by column type.

Column Type	FlyQL	Generated SQL
String	message	(message IS NOT NULL AND message != '')
Int / Float	count	(count IS NOT NULL AND count != 0)
Bool	active	active
Date	created_at	(created_at IS NOT NULL)
Negated String	not message	(message IS NULL OR message = '')

Containment (has / not has)

The has operator generates different SQL depending on the column type:

Column Type	FlyQL	Generated SQL
String	message has 'error'	position(message, 'error') > 0
String (negated)	message not has 'error'	(message IS NULL OR position(message, 'error') = 0)
Array	tags has 'web'	has(tags, 'web')
Array (negated)	tags not has 'web'	NOT has(tags, 'web')
Map	metadata has 'key'	mapContains(metadata, 'key')
Map (negated)	metadata not has 'key'	NOT mapContains(metadata, 'key')
Native JSON	data has 'key'	JSONHas(toJSONString(data), 'key')
JSON String	log has 'key'	JSONHas(log, 'key')

JSON Columns

ClickHouse supports two JSON column types with different SQL output.

Native JSON Type

Columns declared as JSON use dot notation with backtick-quoted segments:

FlyQL	Generated SQL
data.name = 'test'	data.`name` = 'test'
data.user.name = 'john'	data.`user`.`name` = 'john'
data.age = 25	data.`age` = 25

JSON String Type

Columns storing JSON as a string (declared with the synthetic jsonstring raw type) use JSONExtract functions wrapped in multiIf for type checking:

FlyQL	Generated SQL
log.name = 'test'	multiIf(JSONType(log, 'name') = 'String', equals(JSONExtractString(log, 'name'), 'test'),0)
log.user.name = 'john'	multiIf(JSONType(log, 'user', 'name') = 'String', equals(JSONExtractString(log, 'user', 'name'), 'john'),0)

Map Columns

Map columns use bracket notation with equals()/notEquals() functions:

FlyQL	Generated SQL
metadata.key1 = 'value1'	equals(metadata['key1'], 'value1')
metadata.key1 != 'value1'	(mapContains(metadata, 'key1') AND notEquals(metadata['key1'], 'value1'))
metadata.pattern ~ 'test.*'	match(metadata['pattern'], 'test.*')

Array Columns

FlyQL array indices are 0-based; the ClickHouse generator adds 1 when emitting SQL because ClickHouse arrays are 1-indexed at the SQL layer:

FlyQL	Generated SQL
tags.0 = 'first'	equals(tags[1], 'first')
tags.1 = 'second'	equals(tags[2], 'second')
tags.0 ~ 'tag.*'	match(tags[1], 'tag.*')

Column Setup

Text/varchar columns whose contents are valid JSON are declared with the synthetic jsonstring raw type:

// Go
columns := map[string]*clickhouse.Column{
    "message": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "message", Type: "String"}),
    "data":    clickhouse.NewColumn(clickhouse.ColumnDef{Name: "data", Type: "JSON"}),       // native JSON
    "log":     clickhouse.NewColumn(clickhouse.ColumnDef{Name: "log", Type: "jsonstring"}),  // JSON-in-text
}

// JavaScript
const columns = {
    message: newColumn({ name: "message", type: "String" }),
    data:    newColumn({ name: "data", type: "JSON" }),        // native JSON
    log:     newColumn({ name: "log", type: "jsonstring" }),    // JSON-in-text
}

Transformers

Transformers generate ClickHouse-specific function calls:

FlyQL	Generated SQL
status|upper = "ERROR"	upper(status) = 'ERROR'
status|lower = "info"	lower(status) = 'info'
message|len > 100	length(message) > 100
message|lower|len > 100	length(lower(message)) > 100
tags|split(",") has "urgent"	has(splitByChar(',', tags), 'urgent')