SQL Dialects
FlyQL generators produce a WHERE clause fragment as a plain SQL string with all values embedded (escaped). The same FlyQL query generates different SQL depending on the target dialect.
Supported Dialects
Section titled “Supported Dialects”- ClickHouse — no column quoting,
match()for regex,JSONExtractfunctions for JSON strings - PostgreSQL — double-quoted identifiers,
~for regex, JSONB operators - StarRocks — backtick-quoted identifiers,
regexp()for regex,parse_json()for JSON strings
Key Differences
Section titled “Key Differences”| Feature | ClickHouse | PostgreSQL | StarRocks |
|---|---|---|---|
| Column quoting | None | "name" | `name` |
| Regex | match(col, 'pat') | "col" ~ 'pat' | regexp(`col`, 'pat') |
| Negated regex | NOT match(col, 'pat') | "col" !~ 'pat' | NOT regexp(`col`, 'pat') |
| Wildcard | col LIKE 'val%' | "col" LIKE 'val%' | `col` LIKE 'val%' |
| Boolean ops | AND / OR | AND / OR | AND / OR |
Empty IN [] | 0 | FALSE | 0 |
Empty NOT IN [] | 1 | TRUE | 1 |
String has | position(col, 'v') > 0 | position('v' in "col") > 0 | INSTR(`col`, 'v') > 0 |
Array has | has(col, 'v') | 'v' = ANY("col") | array_contains(`col`, 'v') |
Map has | mapContains(col, 'k') | "col" ? 'k' | array_contains(map_keys(`col`), 'k') |
JSON has | JSONHas(toJSONString(col), 'k') | "col" ? 'k' | json_exists(...) |
JSON Access — The Biggest Difference
Section titled “JSON Access — The Biggest Difference”Each dialect handles nested JSON columns very differently:
FlyQL input: data.user.name = 'john'
ClickHouse (native JSON column):
data.`user`.`name` = 'john'ClickHouse (JSON stored as string):
multiIf(JSONType(data, 'user', 'name') = 'String', equals(JSONExtractString(data, 'user', 'name'), 'john'), 0)PostgreSQL (JSONB):
(jsonb_typeof("data"->'user'->'name') = 'string' AND "data"->'user'->>'name' = 'john')StarRocks (native JSON):
`data`->'\"user\"'->'\"name\"' = 'john'See each dialect page for complete examples.
Map Access
Section titled “Map Access”| Dialect | FlyQL | Generated SQL |
|---|---|---|
| ClickHouse | metadata.key1 = 'value1' | equals(metadata['key1'], 'value1') |
| PostgreSQL | metadata.key1 = 'value1' | "metadata"->'key1' = 'value1' |
| StarRocks | metadata.key1 = 'value1' | `metadata`['key1'] = 'value1' |
ClickHouse uses equals()/notEquals() functions for Map access. PostgreSQL uses hstore -> operator. StarRocks uses bracket notation directly.
Array Access
Section titled “Array Access”FlyQL array indices are uniformly 0-based. All three dialects use 1-based arrays at the SQL layer, so the generators add 1 to the flyql index before emitting.
| Dialect | FlyQL | Generated SQL |
|---|---|---|
| ClickHouse | tags.0 = 'first' | equals(tags[1], 'first') |
| PostgreSQL | tags.0 = 'first' | "tags"[1] = 'first' |
| StarRocks | tags.0 = 'first' | `tags`[1] = 'first' |
The same FlyQL expression produces different SQL strings per dialect (due to identifier quoting and function syntax) but evaluates to the same result against the same underlying data.
Output Format
Section titled “Output Format”Generators return a WHERE clause fragment — no WHERE keyword, no parameterized placeholders. All values are escaped and embedded directly:
message = 'hello' AND count > 10Not:
WHERE message = ? AND count > ?