join

The join strategy enriches rows from a primary collection with fields from one or more joined collections. It compiles to parallel scans, a join, a field projection, a sort, and a page.

Compiles to: scan + scan → join → project → sort → page

Use this strategy when each row in your result needs fields from another collection — for example, displaying a list of messages alongside the author's name and avatar from a users collection.

Example

domain:
  projections:
    messages_with_authors:
      strategy: join
      primary:
        collection: hello_messages
        as: m
      where:
        "==":
          - var: row.user_id
          - var: principal.subject
      join:
        - collection: users
          as: u
          type: left
          on:
            "==":
              - var: m.user_id
              - var: u.id
          fields:
            - u.name as author_name
            - u.avatar_url as author_avatar_url
      fields:
        - m.id
        - m.message
        - m.created_at
        - m.user_id
        - author_name
        - author_avatar_url
      search:
        arg: q
        fields: [m.message, author_name]
      sort:
        by_arg: sort_by
        order_arg: sort_order
        allowed: [m.created_at, author_name]
        default_by: m.created_at
        default_order: desc
      pagination:
        mode: page
        page_arg: page
        page_size_arg: page_size
        default_page_size: 10
        max_page_size: 50

primary

The primary block names the primary collection and assigns it an alias. The alias is used to disambiguate field references when multiple collections share a field name.

primary:
  collection: hello_messages
  as: m

join

The join array declares one or more collections to join against the primary.

join:
  - collection: users
    as: u
    type: left
    on:
      "==":
        - var: m.user_id
        - var: u.id
    fields:
      - u.name as author_name
      - u.avatar_url as author_avatar_url

Field	Required	Description
collection	Yes	The collection to join
as	Yes	Alias for this collection's fields
type	Yes	left or inner
on	Yes	$expr join condition
fields	Yes	Fields to extract from this collection into the result row

left join — every row from the primary is returned, even if no matching row exists in the joined collection. Joined fields are null for non-matching rows.

inner join — only rows with a matching record in the joined collection are returned.

Field aliasing

Fields extracted from joined collections must be given an alias.

fields:
  - u.name as author_name
  - u.avatar_url as author_avatar_url

The alias becomes the field name in the result row and in subsequent search, sort, and where expressions.

fields

The fields array declares the final output shape of the projection. Only listed fields appear in the result.

fields:
  - m.id
  - m.message
  - m.created_at
  - m.user_id
  - author_name       # from the join above
  - author_avatar_url # from the join above

Fields from the primary collection use <alias>.<field> notation. Joined fields are referenced by their alias (no collection prefix).

where

where on a join projection filters rows after the join is applied. It can reference fields from both the primary and joined collections using their aliases.

where:
  and:
    - "==":
        - var: m.user_id
        - var: principal.subject
    - "!==":
        - var: author_name
        - null

search

search applies full-text filtering across fields from any collection in the join.

search:
  arg: q
  fields: [m.message, author_name]

sort and pagination

Sort and pagination work identically to from_collection. Field references in sort.allowed use the same alias syntax as fields.

Multiple joins

The join array supports multiple entries. Each entry is evaluated against the primary row in the order declared.

join:
  - collection: users
    as: u
    type: left
    on:
      "==": [{ var: m.user_id }, { var: u.id }]
    fields:
      - u.name as author_name

  - collection: channels
    as: c
    type: inner
    on:
      "==": [{ var: m.channel_id }, { var: c.id }]
    fields:
      - c.name as channel_name

What's next

• aggregate: group joined rows and compute metrics
• timeline: build read model state from signal history
• from_collection: simpler strategy for single-collection reads