> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# select

> Specify fields to query with SELECT clause

`select` is a method for specifying which fields to query. You can select fields in a type-safe manner and use SQL aggregate functions and expressions.

## Basic Usage

### Query All Fields

```typescript theme={null}
const users = await puri.table("users").selectAll();

// SELECT * FROM users
```

### Select Specific Fields

```typescript theme={null}
const users = await puri.table("users").select({
  id: "users.id",
  email: "users.email",
  name: "users.name",
});

// SELECT users.id, users.email, users.name FROM users
```

### Field Alias

```typescript theme={null}
const users = await puri.table("users").select({
  userId: "users.id",
  userName: "users.name",
});

// SELECT users.id as userId, users.name as userName FROM users
```

## SQL Aggregate Functions

Puri provides type-safe SQL functions.

### COUNT

```typescript theme={null}
const result = await puri
  .table("users")
  .select({
    total: Puri.count(), // COUNT(*)
  })
  .first();

console.log(result.total); // number

// Count specific column
const result = await puri
  .table("posts")
  .select({
    published: Puri.count("posts.published"),
  })
  .first();
```

### SUM, AVG, MAX, MIN

```typescript theme={null}
const stats = await puri
  .table("orders")
  .select({
    total: Puri.sum("orders.amount"),
    average: Puri.avg("orders.amount"),
    max: Puri.max("orders.amount"),
    min: Puri.min("orders.amount"),
  })
  .first();

console.log(stats.total); // number
console.log(stats.average); // number
```

### String Functions

```typescript theme={null}
const users = await puri.table("users").select({
  id: "users.id",
  full_name: Puri.concat("users.first_name", "' '", "users.last_name"),
  email_upper: Puri.upper("users.email"),
  email_lower: Puri.lower("users.email"),
});
```

## Raw SQL Expressions

You can use complex SQL expressions.

### Type-Specific Raw Functions

```typescript theme={null}
const result = await puri.table("users").select({
  id: "users.id",

  // Return string
  display_name: Puri.rawString("CONCAT(users.first_name, ' ', users.last_name)"),

  // Return number
  age: Puri.rawNumber("YEAR(CURRENT_DATE) - YEAR(users.birth_date)"),

  // Return boolean
  is_adult: Puri.rawBoolean("YEAR(CURRENT_DATE) - YEAR(users.birth_date) >= 18"),

  // Return date
  signup_date: Puri.rawDate("DATE(users.created_at)"),

  // Return string array
  tags: Puri.rawStringArray("ARRAY_AGG(tags.name)"),
});

// Types are automatically inferred
result[0].display_name; // string
result[0].age; // number
result[0].is_adult; // boolean
result[0].signup_date; // Date
result[0].tags; // string[]
```

## Nested Object Selection

You can write SELECT statements with nested object structures.

```typescript theme={null}
const users = await puri
  .table("users")
  .leftJoin("posts", "users.id", "posts.user_id")
  .select({
    id: "users.id",
    email: "users.email",
    post: {
      id: "posts.id",
      title: "posts.title",
    },
  });

// Automatically flattened:
// SELECT
//   users.id,
//   users.email,
//   posts.id as post__id,
//   posts.title as post__title
// FROM users LEFT JOIN posts ...

// Results are hydrated and returned as nested objects
console.log(users[0].post.title);
```

## appendSelect

Adds fields to existing SELECT.

```typescript theme={null}
let query = puri.table("users").select({
  id: "users.id",
  email: "users.email",
});

// Add fields
query = query.appendSelect({
  name: "users.name",
  created_at: "users.created_at",
});

// SELECT users.id, users.email, users.name, users.created_at FROM users
```

<Warning>
  `select()` **overwrites** existing SELECT, while `appendSelect()` **appends** to it.
</Warning>

## Full-Text Search Functions

### ts\_highlight (PostgreSQL)

Highlighting using PostgreSQL's tsvector.

```typescript theme={null}
const posts = await puri
  .table("posts")
  .whereTsSearch("posts.content_tsv", "typescript")
  .select({
    id: "posts.id",
    title: Puri.tsHighlight("posts.title", "typescript"),
    content: Puri.tsHighlight("posts.content", "typescript", {
      startSel: "<mark>",
      stopSel: "</mark>",
      maxFragments: 3,
      maxWords: 50,
    }),
  });
```

**Options:**

* `parser`: Query parser (default: `"websearch_to_tsquery"`)
* `config`: Text search configuration (default: `"simple"`)
* `startSel`: Start tag (default: `"<b>"`)
* `stopSel`: End tag (default: `"</b>"`)
* `maxWords`: Maximum number of words
* `minWords`: Minimum number of words
* `maxFragments`: Maximum number of fragments

### ts\_rank (PostgreSQL)

Calculates search relevance score.

```typescript theme={null}
const posts = await puri
  .table("posts")
  .whereTsSearch("posts.content_tsv", "typescript")
  .select({
    id: "posts.id",
    title: "posts.title",

    // Basic rank
    rank: Puri.tsRank("posts.content_tsv", "typescript"),

    // With weights
    weighted_rank: Puri.tsRank("posts.content_tsv", "typescript", {
      weights: [0.1, 0.2, 0.4, 1.0], // D, C, B, A weights
      normalization: 1, // Length normalization
    }),

    // Cover Density rank
    rank_cd: Puri.tsRankCd("posts.content_tsv", "typescript"),
  })
  .orderBy("rank", "desc");
```

**Options:**

* `parser`: Query parser (default: `"websearch_to_tsquery"`)
* `config`: Text search configuration (default: `"simple"`)
* `weights`: \[D, C, B, A] weight array
* `normalization`: Normalization option (0-32)

### PGroonga Functions

Full-text search using PGroonga extension.

```typescript theme={null}
const posts = await puri
  .table("posts")
  .whereSearch("posts.title", "타입스크립트")
  .select({
    id: "posts.id",

    // Single column highlighting
    title: Puri.highlight("posts.title", "타입스크립트"),

    // Multiple search terms
    content: Puri.highlight("posts.content", ["타입스크립트", "자바스크립트"]),

    // Multiple column highlighting (returns array)
    highlights: Puri.highlight(["posts.title", "posts.content"], ["타입스크립트", "Node.js"]),

    // Search score
    score: Puri.score(),
  })
  .orderBy("score", "desc");

// Types are automatically inferred
console.log(posts[0].title); // string
console.log(posts[0].highlights); // string[]
console.log(posts[0].score); // number
```

### pg\_trgm Similarity Functions

Calculates similarity scores provided by PostgreSQL's `pg_trgm` extension in SELECT. After filtering candidates with `whereFuzzy`, these functions can be used to compute per-column weighted scores.

```typescript theme={null}
const results = await puri
  .table("items")
  .whereFuzzy("items.search_text", query)
  .select({
    id: "items.id",
    title_ko: "items.title_ko",
    title_en: "items.title_en",

    // word similarity — how similar the query is to a substring of the column (0~1)
    title_score: Puri.wordSimilarity("items.title_ko", query),

    // similarity — overall similarity between two strings (0~1)
    full_score: Puri.similarity("items.title_ko", query),

    // strict word similarity — stricter substring matching than word similarity (0~1)
    strict_score: Puri.strictWordSimilarity("items.title_ko", query),
  })
  .orderBy("title_score", "desc");

// Types are automatically inferred
results[0].title_score; // number
results[0].full_score; // number
results[0].strict_score; // number
```

**Weighted score example:**

The return values of similarity functions (`SqlExpression<"number">`) can be combined with `rawNumber` to compute per-column weighted scores.

```typescript theme={null}
const results = await puri
  .table("items")
  .whereFuzzy("items.search_text", query)
  .select({
    id: "items.id",
    title_ko: "items.title_ko",

    // Individual similarity scores (for weighted scoring)
    ko_score: Puri.wordSimilarity("items.title_ko", query),
    en_score: Puri.wordSimilarity("items.title_en", query),
  })
  .orderBy("ko_score", "desc");
```

**Function comparison:**

| Function                                   | PostgreSQL function                     | Purpose                                       |
| ------------------------------------------ | --------------------------------------- | --------------------------------------------- |
| `Puri.wordSimilarity(column, query)`       | `word_similarity(query, column)`        | Substring similarity between query and column |
| `Puri.similarity(column, query)`           | `similarity(column, query)`             | Overall string similarity                     |
| `Puri.strictWordSimilarity(column, query)` | `strict_word_similarity(query, column)` | Strict substring similarity                   |

<Info>
  These functions return `SqlExpression<"number">` and can be used within `select`. The `pg_trgm` extension must be installed.
</Info>

## Practical Examples

<Tabs>
  <Tab title="User Statistics" icon="chart-bar">
    ```typescript theme={null}
    const stats = await puri.table("users")
      .select({
        total_users: Puri.count(),
        active_users: Puri.rawNumber(
          "SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)"
        ),
        avg_age: Puri.avg("users.age"),
        oldest: Puri.max("users.age"),
        youngest: Puri.min("users.age")
      })
      .first();

    console.log(stats);
    // {
    //   total_users: 1000,
    //   active_users: 850,
    //   avg_age: 32.5,
    //   oldest: 65,
    //   youngest: 18
    // }
    ```
  </Tab>

  <Tab title="Order Summary" icon="cart-shopping">
    ```typescript theme={null}
    const orderSummary = await puri.table("orders")
      .where("orders.status", "completed")
      .select({
        order_count: Puri.count(),
        total_amount: Puri.sum("orders.amount"),
        avg_amount: Puri.avg("orders.amount"),
        max_amount: Puri.max("orders.amount"),

        // Date format conversion
        first_order: Puri.rawDate("MIN(orders.created_at)"),
        last_order: Puri.rawDate("MAX(orders.created_at)")
      })
      .first();
    ```
  </Tab>

  <Tab title="Users + Posts" icon="user-pen">
    ```typescript theme={null}
    const users = await puri.table("users")
      .leftJoin("posts", "users.id", "posts.user_id")
      .select({
        // User information
        id: "users.id",
        name: "users.name",
        email: "users.email",

        // Post information (nested)
        post: {
          id: "posts.id",
          title: "posts.title",
          views: "posts.views"
        },

        // Calculated field
        full_info: Puri.concat(
          "users.name",
          "' ('",
          "users.email",
          "')'"
        )
      });

    // Returned as nested object
    console.log(users[0].post.title);
    ```
  </Tab>

  <Tab title="Search + Highlighting" icon="magnifying-glass">
    ```typescript theme={null}
    const searchResults = await puri.table("posts")
      .whereTsSearch("posts.content_tsv", "typescript programming")
      .select({
        id: "posts.id",

        // Title highlighting
        title: Puri.tsHighlight("posts.title", "typescript programming", {
          startSel: "<mark>",
          stopSel: "</mark>"
        }),

        // Content excerpt + highlighting
        excerpt: Puri.tsHighlight("posts.content", "typescript programming", {
          startSel: "<mark>",
          stopSel: "</mark>",
          maxFragments: 2,
          maxWords: 30
        }),

        // Relevance score
        rank: Puri.tsRank("posts.content_tsv", "typescript programming", {
          weights: [0.1, 0.2, 0.4, 1.0]
        })
      })
      .orderBy("rank", "desc")
      .limit(20);

    // Returns highlighted HTML
    console.log(searchResults[0].title);
    // "Introduction to <mark>TypeScript</mark> <mark>Programming</mark>"
    ```
  </Tab>
</Tabs>

## Type Safety

SELECT return types are automatically inferred.

```typescript theme={null}
const result = await puri
  .table("users")
  .select({
    id: "users.id",
    email: "users.email",
    count: Puri.count(),
    upper_name: Puri.upper("users.name"),
  })
  .first();

// Types are automatically inferred
result.id; // number
result.email; // string
result.count; // number
result.upper_name; // string

result.unknown; // ❌ Type error
```

## Cautions

### 1. select vs appendSelect

```typescript theme={null}
// ❌ Bad: Previous select is overwritten
query.select({ id: "users.id" }).select({ name: "users.name" }); // id is lost!

// ✅ Good: Add with appendSelect
query.select({ id: "users.id" }).appendSelect({ name: "users.name" });
```

### 2. Table Prefix Required

```typescript theme={null}
// ❌ Wrong
puri.table("users").select({
  id: "id", // Unclear which table's id
});

// ✅ Correct
puri.table("users").select({
  id: "users.id", // Explicit table specification
});
```

### 3. Specify Raw SQL Types

```typescript theme={null}
// Specify return type when using raw SQL
const result = await puri.table("users").select({
  // ✅ Type specified
  age: Puri.rawNumber("YEAR(CURRENT_DATE) - YEAR(birth_date)"),

  // ❌ No type (any)
  unknown: puri.raw("SOME_COMPLEX_SQL"),
});
```

## Next Steps

<CardGroup cols={2}>
  <Card title="where" icon="filter" href="/en/api-reference/puri-methods/where">
    Filter with conditions
  </Card>

  <Card title="join" icon="link" href="/en/api-reference/puri-methods/join">
    Join tables
  </Card>

  <Card title="order-by" icon="arrow-down-1-9" href="/en/api-reference/puri-methods/order-by">
    Sort results
  </Card>

  <Card title="advanced-methods" icon="wand-magic-sparkles" href="/en/api-reference/puri-methods/advanced-methods">
    Advanced query methods
  </Card>
</CardGroup>