# Moose / Olap / Read Data Documentation – TypeScript

## Included Files
1. moose/olap/read-data/read-data.mdx

## Querying Data

Source: moose/olap/read-data/read-data.mdx

Query OLAP tables using SQL with type safety

# Querying Data

Moose provides type-safe SQL querying for your `OlapTable` and `MaterializedView` instances. Use cases include:

- Building APIs to expose your data to client/frontend applications
- Building transformation pipelines inside your database with materialized views

## Querying with MooseClient

Use `MooseClient` to query data from existing tables and materialized views.

### Basic Querying

```ts filename="BasicQuerying.ts"

const client = new MooseClient();

// Query existing table
const query = sql`
  SELECT id, name, email
  FROM ${UserTable}
  WHERE status = 'active'
  LIMIT 10
`;

const result = await client.query.execute(query);
const data = await result.json();
```

### Querying Materialized Views

```ts filename="QueryMaterializedView.ts"

const client = new MooseClient();

// Query existing materialized view
const query = sql`
  SELECT user_id, total_orders, average_order_value
  FROM user_stats_view
  WHERE total_orders > 10
  ORDER BY average_order_value DESC
`;

const result = await client.query.execute(query);
```

## Select With Column and Table References

```ts filename="TypedReferences.ts"

// Reference table columns with type safety
const cols = UserTable.columns;
const query = sql`
  SELECT 
    ${cols.id},
    ${cols.name},
    ${cols.email}
  FROM ${UserTable}
  WHERE ${cols.status} = 'active'
`;

// Multiple table references
const joinQuery = sql`
  SELECT 
    ${UserTable.columns.id},
    ${UserTable.columns.name},
    ${OrderTable.columns.order_value}
  FROM ${UserTable}
  JOIN ${OrderTable} ON ${UserTable.columns.id} = ${OrderTable.columns.user_id}
`;
```

When you query a materialized view, you reference the `MaterializedView.targetTable` to get the columns of the target table.

```ts filename="TypedReferences.ts"

const query = sql`
  SELECT 
    ${ExampleMaterializedView.targetTable.columns.id},
    ${ExampleMaterializedView.targetTable.columns.name},
    ${ExampleMaterializedView.targetTable.columns.email}
  FROM ${ExampleMaterializedView.targetTable}
`;
```

In ClickHouse, when you query a Materialized View that has columns of type `AggregateFunction` in the result set, ordinarily you would need to run:

```sql
SELECT sumMerge(amount) FROM {ExampleMaterializedView}
```

When querying this with Moose, you can just reference the column name in the `sql` template literal. The interpolation will be replaced with the correct ClickHouse function:

```ts filename="TypedReferences.ts"

const query = sql`
  SELECT ${ExampleMaterializedView.targetTable.columns.amount}
  FROM ${ExampleMaterializedView.targetTable}
`;

// This will be replaced with:
// SELECT sumMerge(amount) FROM {ExampleMaterializedView}
```

## Filtering with WHERE Clauses

```ts filename="WhereClauses.ts"

// Multiple WHERE conditions
const filterQuery = sql`
  SELECT ${UserTable.columns.id}, ${UserTable.columns.name}
  FROM ${UserTable}
  WHERE ${UserTable.columns.status} = 'active'
    AND ${UserTable.columns.created_at} > '2024-01-01'
    AND ${UserTable.columns.email} ILIKE ${'%' + searchTerm + '%'}
`;

// Using IN clauses
const inQuery = sql`
  SELECT * FROM ${UserTable}
  WHERE ${UserTable.columns.id} IN (${userIds})
`;

// Using BETWEEN
const rangeQuery = sql`
  SELECT * FROM ${UserTable}
  WHERE ${UserTable.columns.age} BETWEEN ${minAge} AND ${maxAge}
`;
```

## Dynamic Query Building

Use the `sql` template literal to build safe queries:

```ts filename="SqlTemplateLiterals.ts"

// Safe interpolation with sql template literal
const status = 'active';
const limit = 10;

const query = sql`
  SELECT id, name, email
  FROM ${UserTable}
  WHERE ${UserTable.columns.status} = ${status}
  LIMIT ${limit}
`;

// Conditional WHERE clauses
interface FilterParams {
  minAge?: number;
  status?: "active" | "inactive";
  searchText?: string;
}

const buildConditionalQuery = (filters: FilterParams) => {
  let conditions = [];

  if (filters.minAge !== undefined) {
    conditions.push(sql`age >= ${filters.minAge}`);
  }

  if (filters.status) {
    conditions.push(sql`status = ${filters.status}`);
  }

  if (filters.searchText) {
    conditions.push(sql`(name ILIKE ${'%' + filters.searchText + '%'} OR email ILIKE ${'%' + filters.searchText + '%'})`);
  }

  let query = sql`SELECT * FROM ${UserTable}`;

  if (conditions.length > 0) {
    query = sql`${query} WHERE ${conditions.join(' AND ')}`;
  }

  return sql`${query} ORDER BY created_at DESC`;
};
```

## Building APIs

To build REST APIs that expose your data, see the [Bring Your Own API Framework documentation](/moose/app-api-frameworks) for comprehensive examples and patterns using Express, Koa, Fastify, or FastAPI.

## Common Pitfalls

- **Column name typos**: Use `UserTable.columns.columnName` for autocomplete
- **Type mismatches**: Ensure your schema types match ClickHouse types
- **Missing imports**: Import your table definitions before using them
- **Template literal syntax**: Use backticks `sql` not regular strings
- **Forgetting await**: Always await `client.query.execute()`

## Performance Optimization
If your query is slower than expected, there are a few things you can check:

- If using filters, try to filter on a column that is defined in the `orderByFields` of the table
- For common queries, consider [creating a materialized view](/stack/olap/create-materialized-view) to pre-compute the result set

## Further Reading