Query Recipes

This page collects ten common FlyQL query patterns. Each recipe shows the FlyQL query, then the full parse → generate → run code in Python, Go, and JavaScript, with a “See also” link to the relevant reference page. Every snippet is a real file under each language’s snippets/recipes/ directory and is runnable stand-alone.

Filter records where status is exactly 200

A simple equality filter. Both sides of = are required — the key on the left and the value on the right.

status = 200

Python

"""Filter records where status field is exactly 200.

FlyQL query: ``status = 200``
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse("status = 200")

columns = {
    "status": Column("status", "UInt32"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter records where status field is exactly 200.
//
// FlyQL query: status = 200
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse("status = 200")
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "status": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "status", Type: "UInt32"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter records where status field is exactly 200.
//
// FlyQL query: status = 200

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse('status = 200')

const columns = {
    status: newColumn({ name: 'status', type: 'UInt32' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Comparison Operators for the full operator list.

Filter active records that are either high priority or critical severity

A truthy check (active) combined with a parenthesized or group. The parentheses are required: without them, default precedence (and binds tighter than or) would change the meaning.

active and (priority = 'high' or severity = 'critical')

Python

"""Filter active records that are either high priority or critical severity.

FlyQL query: ``active and (priority = 'high' or severity = 'critical')``
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse("active and (priority = 'high' or severity = 'critical')")

columns = {
    "active": Column("active", "Bool"),
    "priority": Column("priority", "String"),
    "severity": Column("severity", "String"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter active records that are either high priority or critical severity.
//
// FlyQL query: active and (priority = 'high' or severity = 'critical')
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse("active and (priority = 'high' or severity = 'critical')")
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "active":   clickhouse.NewColumn(clickhouse.ColumnDef{Name: "active", Type: "Bool"}),
    "priority": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "priority", Type: "String"}),
    "severity": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "severity", Type: "String"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter active records that are either high priority or critical severity.
//
// FlyQL query: active and (priority = 'high' or severity = 'critical')

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse("active and (priority = 'high' or severity = 'critical')")

const columns = {
    active: newColumn({ name: 'active', type: 'Bool' }),
    priority: newColumn({ name: 'priority', type: 'String' }),
    severity: newColumn({ name: 'severity', type: 'String' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Boolean Logic for precedence rules and grouping.

Filter records by price greater than 50.00 and quantity at most 10

Two numeric range comparisons joined by and. Decimal literals like 50.00 parse as floats; integer literals like 10 parse as ints.

price > 50.00 and quantity <= 10

Python

"""Filter records by price greater than 50.00 and quantity at most 10.

FlyQL query: ``price > 50.00 and quantity <= 10``
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse("price > 50.00 and quantity <= 10")

columns = {
    "price": Column("price", "Float64"),
    "quantity": Column("quantity", "UInt32"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter records by price greater than 50.00 and quantity at most 10.
//
// FlyQL query: price > 50.00 and quantity <= 10
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse("price > 50.00 and quantity <= 10")
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "price":    clickhouse.NewColumn(clickhouse.ColumnDef{Name: "price", Type: "Float64"}),
    "quantity": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "quantity", Type: "UInt32"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter records by price greater than 50.00 and quantity at most 10.
//
// FlyQL query: price > 50.00 and quantity <= 10

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse('price > 50.00 and quantity <= 10')

const columns = {
    price: newColumn({ name: 'price', type: 'Float64' }),
    quantity: newColumn({ name: 'quantity', type: 'UInt32' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Comparison Operators and Values & Types for literal type rules.

Filter records where event_type is either 'login' or 'logout'

Two equality checks against the same column joined by or. Note the mandatory single quotes around 'login' and 'logout': without quotes, FlyQL would interpret login as a column reference, not a string literal.

event_type = 'login' or event_type = 'logout'

For lists of values, prefer the in form: event_type in ['login', 'logout'].

Python

"""Filter records where event_type is either 'login' or 'logout'.

FlyQL query: ``event_type = 'login' or event_type = 'logout'``

Note: text values like ``'login'`` MUST be quoted. Without quotes, FlyQL would
treat ``login`` as a column reference. See ``syntax/values`` for details.
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse("event_type = 'login' or event_type = 'logout'")

columns = {
    "event_type": Column("event_type", "String"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter records where event_type is either 'login' or 'logout'.
//
// FlyQL query: event_type = 'login' or event_type = 'logout'
//
// Text values like 'login' MUST be quoted; without quotes, FlyQL would treat
// `login` as a column reference. See syntax/values for details.
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse("event_type = 'login' or event_type = 'logout'")
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "event_type": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "event_type", Type: "String"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter records where event_type is either 'login' or 'logout'.
//
// FlyQL query: event_type = 'login' or event_type = 'logout'
//
// Text values like 'login' MUST be quoted; without quotes, FlyQL would treat
// `login` as a column reference. See syntax/values for details.

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse("event_type = 'login' or event_type = 'logout'")

const columns = {
    event_type: newColumn({ name: 'event_type', type: 'String' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Values & Types — String for the string-quoting rule.

Filter accounts that are enabled, not suspended, and have a last login set

Three truthy/falsy terms chained with and. A bare key (no operator, no value) is a truthy check; not suspended negates the truthy check into a falsy check.

enabled and not suspended and last_login

Python

"""Filter accounts that are enabled, not suspended, and have a last_login set.

FlyQL query: ``enabled and not suspended and last_login``

All three terms use truthy/falsy semantics: a bare key (no operator, no value)
means "this field is truthy". ``not suspended`` flips the truthy check into a
falsy check.
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse("enabled and not suspended and last_login")

columns = {
    "enabled": Column("enabled", "Bool"),
    "suspended": Column("suspended", "Bool"),
    "last_login": Column("last_login", "DateTime"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter accounts that are enabled, not suspended, and have a last_login set.
//
// FlyQL query: enabled and not suspended and last_login
//
// All three terms use truthy/falsy semantics: a bare key (no operator, no
// value) means "this field is truthy". `not suspended` flips the truthy check
// into a falsy check.
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse("enabled and not suspended and last_login")
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "enabled":    clickhouse.NewColumn(clickhouse.ColumnDef{Name: "enabled", Type: "Bool"}),
    "suspended":  clickhouse.NewColumn(clickhouse.ColumnDef{Name: "suspended", Type: "Bool"}),
    "last_login": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "last_login", Type: "DateTime"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter accounts that are enabled, not suspended, and have a last_login set.
//
// FlyQL query: enabled and not suspended and last_login
//
// All three terms use truthy/falsy semantics: a bare key (no operator, no
// value) means "this field is truthy". `not suspended` flips the truthy check
// into a falsy check.

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse('enabled and not suspended and last_login')

const columns = {
    enabled: newColumn({ name: 'enabled', type: 'Bool' }),
    suspended: newColumn({ name: 'suspended', type: 'Bool' }),
    last_login: newColumn({ name: 'last_login', type: 'DateTime' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Values & Types — Truthy/Falsy.

Filter messages that match (?i)error but not (?i)warning

Regex match (~) and regex non-match (!~). The (?i) inline flag makes the match case-insensitive.

message ~ "(?i)error" and message !~ "(?i)warning"

Python

"""Filter messages that match (case-insensitive) 'error' but not 'warning'.

FlyQL query: ``message ~ "(?i)error" and message !~ "(?i)warning"``

The ``~`` operator is regex match; ``!~`` is regex non-match. The ``(?i)`` inline
flag enables case-insensitive matching.
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse('message ~ "(?i)error" and message !~ "(?i)warning"')

columns = {
    "message": Column("message", "String"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter messages that match (case-insensitive) 'error' but not 'warning'.
//
// FlyQL query: message ~ "(?i)error" and message !~ "(?i)warning"
//
// The `~` operator is regex match; `!~` is regex non-match. The `(?i)` inline
// flag enables case-insensitive matching.
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse(`message ~ "(?i)error" and message !~ "(?i)warning"`)
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "message": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "message", Type: "String"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter messages that match (case-insensitive) 'error' but not 'warning'.
//
// FlyQL query: message ~ "(?i)error" and message !~ "(?i)warning"
//
// The `~` operator is regex match; `!~` is regex non-match. The `(?i)` inline
// flag enables case-insensitive matching.

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse('message ~ "(?i)error" and message !~ "(?i)warning"')

const columns = {
    message: newColumn({ name: 'message', type: 'String' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Pattern Matching for regex flags, escapes, and dialect notes.

Filter pending tasks that are either past due or urgent

A truthy check on pending combined with a parenthesized or group. The date comparison due_date < '2023-12-31' uses an ISO-8601 string literal; ClickHouse, PostgreSQL, and StarRocks all coerce it to a DATE value at the database boundary.

pending and (due_date < '2023-12-31' or priority = 'urgent')

Python

"""Filter pending tasks that are either past due or urgent.

FlyQL query: ``pending and (due_date < '2023-12-31' or priority = 'urgent')``

Date comparisons use ISO-8601 string literals; ClickHouse, PostgreSQL, and
StarRocks all coerce them to DATE/DATETIME values. See ``syntax/dates`` for
when to use string literals versus temporal functions like ``ago(...)``.
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse("pending and (due_date < '2023-12-31' or priority = 'urgent')")

columns = {
    "pending": Column("pending", "Bool"),
    "due_date": Column("due_date", "Date"),
    "priority": Column("priority", "String"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter pending tasks that are either past due or urgent.
//
// FlyQL query: pending and (due_date < '2023-12-31' or priority = 'urgent')
//
// Date comparisons use ISO-8601 string literals; ClickHouse, PostgreSQL, and
// StarRocks all coerce them to DATE/DATETIME values. See syntax/dates for when
// to use string literals versus temporal functions like ago(...).
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse("pending and (due_date < '2023-12-31' or priority = 'urgent')")
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "pending":  clickhouse.NewColumn(clickhouse.ColumnDef{Name: "pending", Type: "Bool"}),
    "due_date": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "due_date", Type: "Date"}),
    "priority": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "priority", Type: "String"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter pending tasks that are either past due or urgent.
//
// FlyQL query: pending and (due_date < '2023-12-31' or priority = 'urgent')
//
// Date comparisons use ISO-8601 string literals; ClickHouse, PostgreSQL, and
// StarRocks all coerce them to DATE/DATETIME values. See syntax/dates for when
// to use string literals versus temporal functions like ago(...).

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse("pending and (due_date < '2023-12-31' or priority = 'urgent')")

const columns = {
    pending: newColumn({ name: 'pending', type: 'Bool' }),
    due_date: newColumn({ name: 'due_date', type: 'Date' }),
    priority: newColumn({ name: 'priority', type: 'String' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Dates for string-literal vs. temporal-function date semantics.

Filter products that are in stock, in selected categories, with high rating

Combines three operator types in one query: a truthy check (in_stock), list membership (in), and a numeric comparison (>).

in_stock and category in ['electronics', 'appliances'] and rating > 4.5

Python

"""Filter products that are in stock, in selected categories, with high rating.

FlyQL query: ``in_stock and category in ['electronics', 'appliances'] and rating > 4.5``

Combines truthy (``in_stock``), list membership (``in [...]``), and a numeric
comparison in a single query.
"""

from flyql import parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

result = parse(
    "in_stock and category in ['electronics', 'appliances'] and rating > 4.5"
)

columns = {
    "in_stock": Column("in_stock", "Bool"),
    "category": Column("category", "String"),
    "rating": Column("rating", "Float64"),
}

sql = to_sql_where(result.root, columns)
print(sql)

Go

// Filter products that are in stock, in selected categories, with high rating.
//
// FlyQL query:
//
//  in_stock and category in ['electronics', 'appliances'] and rating > 4.5
//
// Combines truthy (in_stock), list membership (in [...]), and a numeric
// comparison in a single query.
package main

import (
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func main() {
  result, err := flyql.Parse(
    "in_stock and category in ['electronics', 'appliances'] and rating > 4.5",
  )
  if err != nil {
    panic(err)
  }

  columns := map[string]*clickhouse.Column{
    "in_stock": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "in_stock", Type: "Bool"}),
    "category": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "category", Type: "String"}),
    "rating":   clickhouse.NewColumn(clickhouse.ColumnDef{Name: "rating", Type: "Float64"}),
  }

  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  fmt.Println(sql)
}

JavaScript

// Filter products that are in stock, in selected categories, with high rating.
//
// FlyQL query:
//   in_stock and category in ['electronics', 'appliances'] and rating > 4.5
//
// Combines truthy (in_stock), list membership (in [...]), and a numeric
// comparison in a single query.

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const result = parse("in_stock and category in ['electronics', 'appliances'] and rating > 4.5")

const columns = {
    in_stock: newColumn({ name: 'in_stock', type: 'Bool' }),
    category: newColumn({ name: 'category', type: 'String' }),
    rating: newColumn({ name: 'rating', type: 'Float64' }),
}

const sql = generateWhere(result.root, columns)
console.log(sql)

See also: Lists for in / not in semantics.

Walk the AST to emit a custom (non-SQL) generator

The same parsed AST that powers the SQL generators can be walked to produce any target — JSON, Elasticsearch DSL, MongoDB filters, log grep commands, and so on. This recipe walks status = 200 and env in ['prod', 'staging'] and emits an Elasticsearch Query DSL object.

status = 200 and env in ['prod', 'staging']

Python

"""Walk the FlyQL AST and emit an Elasticsearch Query DSL object.

FlyQL query: ``status = 200 and env in ['prod', 'staging']``

Demonstrates a non-SQL custom generator: same AST, different target.
See ``advanced/ast`` for the full custom-generator walkthrough.
"""

import json

from flyql import parse
from flyql.core.constants import BoolOperator, Operator


def generate_es(node):
    if node is None:
        return {"match_all": {}}

    if node.expression is not None:
        result = expression_to_es(node.expression)
    else:
        left = generate_es(node.left)
        right = generate_es(node.right)
        if node.bool_operator == BoolOperator.AND.value:
            result = {"bool": {"must": [left, right]}}
        else:
            result = {"bool": {"should": [left, right], "minimum_should_match": 1}}

    if node.negated:
        result = {"bool": {"must_not": [result]}}

    return result


def expression_to_es(expr):
    field = expr.key.raw
    op = expr.operator
    if op == Operator.EQUALS.value:
        return {"term": {field: expr.value}}
    if op == Operator.IN.value:
        return {"terms": {field: expr.values or []}}
    if op == Operator.GREATER_THAN.value:
        return {"range": {field: {"gt": expr.value}}}
    if op == Operator.TRUTHY.value:
        return {"exists": {"field": field}}
    raise ValueError(f"unsupported operator: {op}")


result = parse("status = 200 and env in ['prod', 'staging']")
es_query = generate_es(result.root)
print(json.dumps(es_query, indent=2))

Go

// Walk the FlyQL AST and emit an Elasticsearch Query DSL object.
//
// FlyQL query: status = 200 and env in ['prod', 'staging']
//
// Demonstrates a non-SQL custom generator: same AST, different target.
// See advanced/ast for the full custom-generator walkthrough.
package main

import (
  "encoding/json"
  "fmt"

  flyql "github.com/iamtelescope/flyql/golang"
)

func generateES(node *flyql.Node) map[string]any {
  if node == nil {
    return map[string]any{"match_all": map[string]any{}}
  }

  var result map[string]any
  if node.Expression != nil {
    result = expressionToES(node.Expression)
  } else {
    left := generateES(node.Left)
    right := generateES(node.Right)
    if node.BoolOperator == flyql.BoolOpAnd {
      result = map[string]any{"bool": map[string]any{"must": []any{left, right}}}
    } else {
      result = map[string]any{
        "bool": map[string]any{"should": []any{left, right}, "minimum_should_match": 1},
      }
    }
  }

  if node.Negated {
    result = map[string]any{"bool": map[string]any{"must_not": []any{result}}}
  }
  return result
}

func expressionToES(expr *flyql.Expression) map[string]any {
  field := expr.Key.Raw
  switch expr.Operator {
  case flyql.OpEquals:
    return map[string]any{"term": map[string]any{field: expr.Value}}
  case flyql.OpIn:
    return map[string]any{"terms": map[string]any{field: expr.Values}}
  case flyql.OpGreater:
    return map[string]any{"range": map[string]any{field: map[string]any{"gt": expr.Value}}}
  case flyql.OpTruthy:
    return map[string]any{"exists": map[string]any{"field": field}}
  default:
    panic(fmt.Sprintf("unsupported operator: %s", expr.Operator))
  }
}

func main() {
  result, err := flyql.Parse("status = 200 and env in ['prod', 'staging']")
  if err != nil {
    panic(err)
  }
  esQuery := generateES(result.Root)
  data, _ := json.MarshalIndent(esQuery, "", "  ")
  fmt.Println(string(data))
}

JavaScript

// Walk the FlyQL AST and emit an Elasticsearch Query DSL object.
//
// FlyQL query: status = 200 and env in ['prod', 'staging']
//
// Demonstrates a non-SQL custom generator: same AST, different target.
// See advanced/ast for the full custom-generator walkthrough.

import { BoolOperator, Operator, parse } from 'flyql'

function generateES(node) {
    if (!node) {
        return { match_all: {} }
    }

    let result
    if (node.expression) {
        result = expressionToES(node.expression)
    } else {
        const left = generateES(node.left)
        const right = generateES(node.right)
        if (node.boolOperator === BoolOperator.AND) {
            result = { bool: { must: [left, right] } }
        } else {
            result = { bool: { should: [left, right], minimum_should_match: 1 } }
        }
    }

    if (node.negated) {
        result = { bool: { must_not: [result] } }
    }
    return result
}

function expressionToES(expr) {
    const field = expr.key.raw
    switch (expr.operator) {
        case Operator.EQUALS:
            return { term: { [field]: expr.value } }
        case Operator.IN:
            return { terms: { [field]: expr.values || [] } }
        case Operator.GREATER_THAN:
            return { range: { [field]: { gt: expr.value } } }
        case Operator.TRUTHY:
            return { exists: { field } }
        default:
            throw new Error(`unsupported operator: ${expr.operator}`)
    }
}

const result = parse("status = 200 and env in ['prod', 'staging']")
const esQuery = generateES(result.root)
console.log(JSON.stringify(esQuery, null, 2))

See also: AST & Custom Generators for the full node-type reference and a longer walkthrough.

IN-list type consistency

The parser always accepts heterogeneous lists like [200, 'ok', 404]. SQL generators run a type-consistency check only when (a) the column has a declared type, (b) the key is not segmented, and (c) the list is homogeneous (len(set(values_types)) == 1). Heterogeneous lists, segmented keys, and untyped columns all bypass the check at the call site.

The recipe demonstrates three cases against typed columns: a homogeneous match, a heterogeneous bypass, and a homogeneous wrong-type case that raises the canonical error type mismatch in IN list: .... The error string is identical across Python, Go, and JavaScript.

Python

"""Demonstrate IN-list type validation across three cases.

FlyQL parsers always accept heterogeneous lists. SQL generators run a
type-consistency check ONLY when (a) the column has a declared type, (b) the
key is not segmented, and (c) the list is homogeneous. Heterogeneous lists
bypass the check at the call site and pass through.

Three demonstrated cases:
  (a) Homogeneous list matching column type — SQL is generated.
  (b) Heterogeneous list against a typed column — SQL is generated (validator
      is skipped because ``len(set(values_types)) > 1``).
  (c) Homogeneous list with the wrong element type — FlyqlError is raised with
      the canonical message ``type mismatch in IN list: ...``.
"""

from flyql import FlyqlError, parse
from flyql.generators.clickhouse.column import Column
from flyql.generators.clickhouse.generator import to_sql_where

columns = {
    "category": Column("category", "String"),
    "status": Column("status", "UInt32"),
}

# (a) Homogeneous list matching column type — SQL is generated.
result_a = parse("category in ['electronics', 'appliances']")
print("(a)", to_sql_where(result_a.root, columns))

# (b) Heterogeneous list — validator is skipped, SQL is generated.
result_b = parse("status in [200, 'ok', 404]")
print("(b)", to_sql_where(result_b.root, columns))

# (c) Homogeneous wrong-type list — FlyqlError is raised.
result_c = parse("status in ['ok', 'fail']")
try:
    to_sql_where(result_c.root, columns)
except FlyqlError as exc:
    print("(c)", exc)

Go

// Demonstrate IN-list type validation across three cases.
//
// FlyQL parsers always accept heterogeneous lists. SQL generators run a
// type-consistency check ONLY when (a) the column has a declared type, (b) the
// key is not segmented, and (c) the list is homogeneous. Heterogeneous lists
// bypass the check at the call site and pass through.
//
// Three demonstrated cases:
//
//  (a) Homogeneous list matching column type — SQL is generated.
//  (b) Heterogeneous list against a typed column — SQL is generated (validator
//      is skipped because the values_types set has more than one entry).
//  (c) Homogeneous list with the wrong element type — error is returned with
//      the canonical message "type mismatch in IN list: ...".
package main

import (
  "fmt"
  "strings"

  flyql "github.com/iamtelescope/flyql/golang"
  "github.com/iamtelescope/flyql/golang/generators/clickhouse"
)

func mustGenerate(query string, columns map[string]*clickhouse.Column) string {
  result, err := flyql.Parse(query)
  if err != nil {
    panic(err)
  }
  sql, err := clickhouse.ToSQLWhere(result.Root, columns)
  if err != nil {
    panic(err)
  }
  return sql
}

func main() {
  columns := map[string]*clickhouse.Column{
    "category": clickhouse.NewColumn(clickhouse.ColumnDef{Name: "category", Type: "String"}),
    "status":   clickhouse.NewColumn(clickhouse.ColumnDef{Name: "status", Type: "UInt32"}),
  }

  // (a) Homogeneous list matching column type — SQL is generated.
  fmt.Println("(a)", mustGenerate("category in ['electronics', 'appliances']", columns))

  // (b) Heterogeneous list — validator is skipped, SQL is generated.
  fmt.Println("(b)", mustGenerate("status in [200, 'ok', 404]", columns))

  // (c) Homogeneous wrong-type list — error is returned.
  resultC, err := flyql.Parse("status in ['ok', 'fail']")
  if err != nil {
    panic(err)
  }
  if _, err := clickhouse.ToSQLWhere(resultC.Root, columns); err != nil {
    if strings.Contains(err.Error(), "type mismatch in IN list:") {
      fmt.Println("(c)", err.Error())
    } else {
      panic(err)
    }
  }
}

JavaScript

// Demonstrate IN-list type validation across three cases.
//
// FlyQL parsers always accept heterogeneous lists. SQL generators run a
// type-consistency check ONLY when (a) the column has a declared type, (b) the
// key is not segmented, and (c) the list is homogeneous. Heterogeneous lists
// bypass the check at the call site and pass through.
//
// Three demonstrated cases:
//   (a) Homogeneous list matching column type — SQL is generated.
//   (b) Heterogeneous list against a typed column — SQL is generated (validator
//       is skipped because the values_types set has more than one entry).
//   (c) Homogeneous list with the wrong element type — error is thrown with
//       the canonical message "type mismatch in IN list: ...".

import { parse } from 'flyql'
import { generateWhere, newColumn } from 'flyql/generators/clickhouse'

const columns = {
    category: newColumn({ name: 'category', type: 'String' }),
    status: newColumn({ name: 'status', type: 'UInt32' }),
}

// (a) Homogeneous list matching column type — SQL is generated.
console.log('(a)', generateWhere(parse("category in ['electronics', 'appliances']").root, columns))

// (b) Heterogeneous list — validator is skipped, SQL is generated.
console.log('(b)', generateWhere(parse("status in [200, 'ok', 404]").root, columns))

// (c) Homogeneous wrong-type list — error is thrown.
try {
    generateWhere(parse("status in ['ok', 'fail']").root, columns)
} catch (err) {
    if (err.message.includes('type mismatch in IN list:')) {
        console.log('(c)', err.message)
    } else {
        throw err
    }
}

See also: Lists — In-List Type Consistency for the full compatibility map and validator semantics.