Query Data (SELECT & JOIN)

Use this page for query-only SQL patterns in KalamDB.

KalamDB supports the DataFusion SELECT query surface. For advanced query syntax and planner behavior, use the DataFusion SQL docs as the primary reference:

• DataFusion SELECT Reference 
• DataFusion SQL Reference 

Basic SELECT

1SELECT <columns>2FROM [<namespace>.]<table_name>3[WHERE <condition>]4[ORDER BY <expr> [ASC|DESC]]5[LIMIT <n>];

Example:

1SELECT id, sender, content, created_at2FROM chat.messages3WHERE conversation_id = 424ORDER BY created_at DESC5LIMIT 50;

Filtering, Grouping, and Aggregation

1SELECT sender, COUNT(*) AS message_count2FROM chat.messages3WHERE conversation_id = 424GROUP BY sender5HAVING COUNT(*) >= 106ORDER BY message_count DESC;

JOIN Support

KalamDB supports standard DataFusion join patterns, including:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN
• JOIN ... USING (...)

Example (INNER JOIN):

1SELECT m.id, m.content, u.email2FROM chat.messages m3INNER JOIN system.users u ON m.user_id = u.user_id4WHERE m.conversation_id = 42;

Example (LEFT JOIN):

1SELECT c.id, c.title, m.content2FROM chat.conversations c3LEFT JOIN chat.messages m ON c.id = m.conversation_id4ORDER BY c.id;

Subqueries and CTEs

Common DataFusion query constructs are supported, including subqueries and CTEs.

1WITH ranked AS (2  SELECT3    sender,4    COUNT(*) AS total_messages5  FROM chat.messages6  GROUP BY sender7)8SELECT *9FROM ranked10WHERE total_messages >= 10011ORDER BY total_messages DESC;

Set Operations

You can use DataFusion set operations such as UNION and UNION ALL.

1SELECT sender FROM chat.messages_20252UNION ALL3SELECT sender FROM chat.messages_2026;

PostgreSQL-style JSON Queries

JSON and JSONB columns can be queried with the same common extraction syntax PostgreSQL users already know.

1SELECT2  doc->>'status' AS status,3  doc->'items'->0 AS first_item4FROM app.orders5WHERE doc->>'status' = 'paid'6  AND doc ? 'customer_id';

Use -> when you want a JSON value, ->> when you want text, and ? when you only need to test key existence.

KalamDB also exposes DataFusion JSON helpers such as json_get, json_as_text, json_length, and json_object_keys for function-style access. For the full JSON function list and current operator coverage, see Built-in Functions.

Advanced Query Reference

For advanced SELECT features, rely on DataFusion SQL documentation:

• SELECT 
• Expressions 
• Joins, CTEs, and Query Composition