Special Columns

Konduit has two column types that store internal IDs but display human-readable values. KSQL handles the translation for you — you can filter, sort, and join on the display value directly.

Person columns

Person columns store user references. In KSQL, you filter and sort by the person's display name — the engine resolves names to internal IDs automatically.

-- Filter by person display name
SELECT * FROM Tasks WHERE Assignee = 'Alice Johnson'

-- Sort by person name
SELECT * FROM Tasks ORDER BY Assignee ASC

-- Group tasks by assignee
SELECT Assignee, COUNT(*) AS task_count
FROM Tasks
GROUP BY Assignee
ORDER BY task_count DESC

In result sets, person columns show user names — not user IDs.

If two users in the workspace have identical display names, the resolver picks one and warns. Use the user-management UI to rename one of them, or use the API endpoints with explicit user IDs.

Relation columns

Relation columns link to rows in other tables. When displayed, they show the linked row's display value (the value from the target table's display column). You can filter and sort by this display value.

-- Filter by the display value of a relation
SELECT * FROM Orders WHERE Customer = 'Acme Corp'

Joining through a relation

Because the relation column exposes the display value, joining is direct:

SELECT o.OrderNumber, c.Name, c.Revenue
FROM Orders o
LEFT JOIN Customers c ON o.Customer = c.Name

This works as long as the target table's display column is Name. If it's something else (e.g. Customer ID), join on that instead.

Multi-row relations

If a relation column links to multiple rows (e.g. a Tasks-table column that links to multiple Customers), it behaves like an array — use the CONTAINS operators to filter:

-- Tasks that include Acme Corp as one of their customers
SELECT * FROM Tasks WHERE Customers CONTAINS 'Acme Corp'

-- Tasks that include all of these customers
SELECT * FROM Tasks WHERE Customers CONTAINS ALL ('Acme Corp', 'Globex')

Tag and multi-select columns

Tag columns are arrays of strings. Use CONTAINS for membership checks:

-- Single tag
SELECT * FROM Issues WHERE Labels CONTAINS 'p1'

-- Any of these tags
SELECT * FROM Products WHERE Categories CONTAINS ANY ('electronics', 'accessories')

-- All these tags must be present
SELECT * FROM Issues WHERE Labels CONTAINS ALL ('frontend', 'backend')

-- Exactly these tags, no more no less
SELECT * FROM Tasks WHERE Tags CONTAINS ONLY ('done', 'reviewed')

-- Exclude
SELECT * FROM Tickets WHERE Tags NOT CONTAINS 'spam'

Display values vs internal IDs

You'll never see internal IDs in result sets — KSQL always resolves person and relation columns to their display value. This is why you can write WHERE Customer = 'Acme Corp' instead of WHERE Customer = 'row_abc123'.

The tradeoff: if you rename a customer (Acme Corp → Acme Corporation), queries that filter on the old name will no longer match. For stable references, use a column on the target table that doesn't change (an autonumber, for example) and join on that.

Navigation