Skip to content

Views & Filters

A View is a read-only window over a table's data, defined by query constraints — where, order_by, limit, offset. A view copies nothing; it just records the constraints and applies them when you read it.

Filter with where

The where string is a ClickHouse expression over the table's columns:

obj_int = await create_object_from_value([1, 2, 3, 4, 5, 6, 7, 8, 9, 10])
print(f"Original data: {await obj_int.data()}\n")  # → [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

# Filter values greater than 5
view_where = obj_int.view(where="value > 5")
print(f"WHERE value > 5: {await view_where.data()}")  # → [6, 7, 8, 9, 10]

# Filter values between 3 and 7
view_where_range = obj_int.view(where="value >= 3 AND value <= 7")
print(f"WHERE value >= 3 AND value <= 7: {await view_where_range.data()}")  # → [3, 4, 5, 6, 7]

# Filter even values
view_where_even = obj_int.view(where="value % 2 = 0")
print(f"WHERE value % 2 = 0 (even): {await view_where_even.data()}")  # → [2, 4, 6, 8, 10]

Sort with order_by

obj_unsorted = await create_object_from_value([50, 20, 80, 10, 40, 60, 30, 70])
print(f"Original data: {await obj_unsorted.data()}\n")  # → [50, 20, 80, 10, 40, 60, 30, 70]

# Sort ascending
view_asc = obj_unsorted.view(order_by="value ASC")
print(f"ORDER BY value ASC: {await view_asc.data()}")  # → [10, 20, 30, 40, 50, 60, 70, 80]

# Sort descending
view_desc = obj_unsorted.view(order_by="value DESC")
print(f"ORDER BY value DESC: {await view_desc.data()}")  # → [80, 70, 60, 50, 40, 30, 20, 10]

Page with limit and offset

obj_nums = await create_object_from_value([10, 20, 30, 40, 50, 60, 70, 80])
print(f"Original data: {await obj_nums.data()}\n")  # → [10, 20, 30, 40, 50, 60, 70, 80]

# Get first 3 elements
view_limit = obj_nums.view(limit=3)
print(f"LIMIT 3: {await view_limit.data()}")  # → [10, 20, 30]

# Skip first 2, get next 3
view_offset_limit = obj_nums.view(offset=2, limit=3)
print(f"OFFSET 2 LIMIT 3: {await view_offset_limit.data()}")  # → [30, 40, 50]

# Skip first 5
view_offset = obj_nums.view(offset=5)
print(f"OFFSET 5: {await view_offset.data()}")  # → [60, 70, 80]

Select a column

Indexing a multi-column Object with a column name returns a view of that column:

obj = await create_object_from_value({"param1": [123, 234, 345], "param2": [456, 567, 678]})
print("Created dict Object with columns: param1, param2")
print(f"Full data: {await obj.data()}\n")  # → {'param1': [123, 234, 345], 'param2': [456, 567, 678]}

# Select param1 using __getitem__
view_param1 = obj["param1"]
print(f"obj['param1'] creates a View: {view_param1}")
print(f"View data: {await view_param1.data()}")  # → [123, 234, 345]

# Select param2
view_param2 = obj["param2"]
print(f"obj['param2'] data: {await view_param2.data()}")  # → [456, 567, 678]

Selected columns behave like any other Object — you can operate on them:

data = await create_object_from_value({"prices": [10, 20, 30, 40, 50], "quantities": [2, 3, 1, 4, 2]})
print(f"Data: {await data.data()}\n")

prices = data["prices"]
quantities = data["quantities"]

# Multiply selected columns
totals = prices * quantities
print(f"prices: {await prices.data()}")  # → [10, 20, 30, 40, 50]
print(f"quantities: {await quantities.data()}")  # → [2, 3, 1, 4, 2]
print(f"prices * quantities = {await totals.data()}")  # → [20, 60, 30, 160, 100]

# Sum of totals
total_sum = await totals.sum()
print(f"Sum of totals: {await total_sum.data()}")  # → 370

Views are read-only

A view references existing data; it has no table of its own. Calling insert() on a view raises RuntimeError.

Next

Building & Combining Data → — copy, concatenate, and append rows.

See Also