Stream-Table JOIN Metadata Enrichment

Business Scenario

A device's reading stream carries only a deviceId and a measurement value — no device attributes such as location or model. These attributes are relatively stable and live in a "metadata table." Requirement: as each reading arrives, join it with the metadata table on deviceId to attach the location/model to the reading and emit them together (a stream-table join).

SQL

SELECT deviceId,
       m.location,
       m.model,
       temperature
FROM stream JOIN meta m ON deviceId = m.deviceId

Input

Device reading stream (stream):

{"deviceId": "d1", "temperature": 31.0}
{"deviceId": "d2", "temperature": 27.5}
{"deviceId": "d9", "temperature": 40.0}

Metadata table (meta, registered via RegisterTable("meta", rows)):

{"deviceId": "d1", "location": "plantA", "model": "TX-100"}
{"deviceId": "d2", "location": "plantB", "model": "TX-200"}

Output

{"deviceId": "d1", "location": "plantA", "model": "TX-100", "temperature": 31.0}
{"deviceId": "d2", "location": "plantB", "model": "TX-200", "temperature": 27.5}

d9 has no match in the metadata table and is dropped by the default INNER JOIN.

Behavior Notes

• Default INNER JOIN: readings without a metadata match are dropped (e.g. d9). To keep unmatched readings, use LEFT JOIN; the unmatched table fields will be nil.
• JOIN executes before WHERE, so WHERE, GROUP BY, and aggregates can all reference joined table fields (e.g. GROUP BY m.location).
• RegisterTable must be called after Execute: first Execute(sql), then ssql.RegisterTable("meta", []map[string]any{...}); otherwise the JOIN cannot find the table and all readings are dropped.
• Table alias m; the match condition is ON deviceId = m.deviceId (no alias on the stream field, table fields use m.xxx).
• The metadata table is a "static dimension table", suited to low-churn data like device/user/product attributes; for high-churn dimensions, refresh the table periodically on the node side.

How to run

A JOIN query is a non-aggregation query; use EmitSync for synchronous results. Note that you must call RegisterTable after Execute to register the metadata table. See How to Run Case SQL for the full boilerplate.

📚 Related Docs

• How to Run Case SQL
• SQL Reference — JOIN syntax
• API Reference — RegisterTable