`queries` — Named Reads

Named, compiled SQL queries served by the query service. No arbitrary SQL — each query is validated against the projection schema at compile time.

queries:
  my_concert_history:
    from: user_concert_stats
    input:
      user_id: { type: string, required: true }
      limit:   { type: integer, required: false }
    fields:
      - user_concert_stats.*
    where:
      user_id:
        eq: input.user_id
    order_by:
      last_checkin_at: desc
    limit: "input.limit ?: 50"

Query fields

Field	Required	Description
`from`	yes	Source projection table name
`input`	no	Named query parameters with types
`fields`	no	Columns to return (default: all)
`where`	no	Filter conditions
`order_by`	no	Sort columns and direction
`limit`	no	Static integer or `"input.param ?: default"`
`joins`	no	Join other projections (max 5)
`group_by`	no	For aggregate queries
`aggregate`	no	Aggregate functions
`distinct`	no	`true` for SELECT DISTINCT
`coalesce_zero`	no	List of columns to replace NULL with 0
`window`	no	Allowed time windows for `gte_window`

`where` operators

Operator	Description
`eq`	Equals
`neq`	Not equals
`gt`	Greater than
`gte`	Greater than or equal
`lt`	Less than
`lte`	Less than or equal
`in`	Value in list
`nin`	Value not in list
`like`	SQL `LIKE` pattern
`ilike`	Case-insensitive `LIKE`
`contains`	Text search (TEXT/STRING columns only)
`is_null`	Column is NULL
`is_not_null`	Column is not NULL
`gte_window`	Time-window filter (requires `window.allowed`)

Joins

Maximum 5 joins per query. type is inner (default), left, right, or full.

Single equality (shorthand):

joins:
  show_directory:
    type: left
    on:
      show_experience_detail.show_id: show_directory.show_id
    fields:
      - show_directory.title
      - show_directory.starts_at

Composite ON (multi-column):

joins:
  parent_rows:
    type: inner
    on:
      pairs:
        - { left: child_rows.tenant_id, right: parent_rows.tenant_id }
        - { left: child_rows.parent_id, right: parent_rows.parent_id }

Up to 16 equalities per join.

Aggregate queries

⚠️

count under fields: is silently dropped. Always use group_by + aggregate:.

queries:
  unread_notification_count:
    from: user_notifications
    input:
      user_id: { type: string, required: true }
    where:
      user_id: { eq: input.user_id }
      read:    { eq: false }
    group_by:
      - user_id                    # single group = scalar result
    aggregate:
      unread_count:
        count: notification_id
    limit: 1

Aggregate functions: count, sum, avg, min, max.

Window queries

For bucketed projections, declare allowed windows:

queries:
  hourly_checkins:
    from: hourly_checkin_counts
    input:
      venue_id: { type: string, required: true }
      window:   { type: string, required: true }
    where:
      venue_id:     { eq: input.venue_id }
      bucket_start: { gte_window: input.window }
    window:
      allowed: ["1h", "24h", "7d"]

Misc query options

# Replace NULL with 0 in result rows
coalesce_zero: [shows_attended, venues_visited]
 
# SELECT DISTINCT (requires explicit fields, cannot combine with group_by/aggregate)
distinct: true

projections relationships

queries — Named Reads