Part 5: Advanced Queries and Hybrid Search

← Part 4: TypeScript Implementation | Part 6: Performance Optimization →

The E-commerce Search That Lost $80K

I was running an e-commerce product search with pure vector similarity. It worked beautifully for semantic matching, but we had a problem:

User searches: "red nike shoes under $100"

Vector search returned:

1. Nike Air Max 270 Black - $189

2. Adidas Ultraboost Red - $180

3. Nike React Blue - $95

Zero of the top 3 results matched the criteria. The results were semantically similar to "shoes," but ignored:

• Color: red ✗

• Brand: nike ✗ (Adidas came up)

• Price: under $100 ✗

Conversion rate on filtered searches: 1.2% (terrible)

The fix: Hybrid search combining vector similarity WITH traditional filters.

After implementing:

// Hybrid: Vector similarity + exact filters
const results = searchProducts("red nike shoes under $100", {
  filters: {
    brand: "Nike",
    color: "red",
    maxPrice: 100,
  },
});

Conversion rate: 1.2% → 11.8%. Revenue from search: +$80k/month.

This article shows you how to build advanced queries that combine the best of both worlds.

The Problem with Pure Vector Search

Vector Search Alone

// Only semantic similarity
const results = await prisma.$queryRaw`
  SELECT * FROM products
  ORDER BY embedding <=> ${queryVector}::vector
  LIMIT 10
`;

Pros:

• ✅ Understands meaning

• ✅ Finds semantically similar items

• ✅ Works with natural language

Cons:

• ❌ Can't enforce exact constraints

• ❌ Ignores critical filters (price, category, availability)

• ❌ No relevance boosting

Traditional Search Alone

SELECT * FROM products
WHERE brand = 'Nike'
  AND color = 'red'
  AND price < 100
  AND name ILIKE '%shoes%';

Pros:

• ✅ Exact filters work perfectly

• ✅ Predictable results

• ✅ Fast with proper indexes

Cons:

• ❌ Keyword matching only

• ❌ Misses semantically similar items

• ❌ User must use exact words

Solution: Hybrid search combining both.

Hybrid Search: Vector + Filters

// src/services/advanced-search.service.ts
import { PrismaClient } from '@prisma/client';
import { EmbeddingService } from './embedding.service';

const prisma = new PrismaClient();

export interface ProductFilters {
  category?: string;
  brand?: string;
  minPrice?: number;
  maxPrice?: number;
  colors?: string[];
  inStock?: boolean;
  tags?: string[];
}

export interface AdvancedSearchOptions {
  query: string;
  filters?: ProductFilters;
  limit?: number;
  minSimilarity?: number;
  useHybrid?: boolean;
}

export class AdvancedSearchService {
  /**
   * Hybrid search with filters
   */
  static async searchProducts(options: AdvancedSearchOptions) {
    const {
      query,
      filters = {},
      limit = 20,
      minSimilarity = 0.6,
      useHybrid = true,
    } = options;
    
    const queryEmbedding = await EmbeddingService.getEmbedding(query);
    
    // Build filter conditions
    const conditions: string[] = ['embedding IS NOT NULL'];
    
    if (filters.category) {
      conditions.push(`category = '${filters.category}'`);
    }
    
    if (filters.brand) {
      conditions.push(`brand = '${filters.brand}'`);
    }
    
    if (filters.minPrice !== undefined) {
      conditions.push(`price >= ${filters.minPrice}`);
    }
    
    if (filters.maxPrice !== undefined) {
      conditions.push(`price <= ${filters.maxPrice}`);
    }
    
    if (filters.colors && filters.colors.length > 0) {
      const colorList = filters.colors.map(c => `'${c}'`).join(',');
      conditions.push(`color = ANY(ARRAY[${colorList}])`);
    }
    
    if (filters.inStock !== undefined) {
      conditions.push(`in_stock = ${filters.inStock}`);
    }
    
    if (filters.tags && filters.tags.length > 0) {
      const tagList = filters.tags.map(t => `'${t}'`).join(',');
      conditions.push(`tags && ARRAY[${tagList}]::text[]`);
    }
    
    const whereClause = conditions.join(' AND ');
    
    if (useHybrid) {
      // Hybrid: Vector + Keyword + Filters
      return await prisma.$queryRaw`
        WITH vector_search AS (
          SELECT 
            id, name, description, brand, price, color,
            1 - (embedding <=> ${queryEmbedding}::vector) as vec_score
          FROM products
          WHERE ${whereClause}
          ORDER BY embedding <=> ${queryEmbedding}::vector
          LIMIT 100
        ),
        keyword_search AS (
          SELECT 
            id, name, description, brand, price, color,
            ts_rank(to_tsvector('english', name || ' ' || description), plainto_tsquery('english', ${query})) as keyword_score
          FROM products
          WHERE ${whereClause}
            AND to_tsvector('english', name || ' ' || description) @@ plainto_tsquery('english', ${query})
          LIMIT 100
        )
        SELECT DISTINCT
          COALESCE(v.id, k.id) as id,
          COALESCE(v.name, k.name) as name,
          COALESCE(v.description, k.description) as description,
          COALESCE(v.brand, k.brand) as brand,
          COALESCE(v.price, k.price) as price,
          COALESCE(v.color, k.color) as color,
          (COALESCE(v.vec_score, 0) * 0.7 + COALESCE(k.keyword_score, 0) * 0.3) as combined_score
        FROM vector_search v
        FULL OUTER JOIN keyword_search k ON v.id = k.id
        WHERE (COALESCE(v.vec_score, 0) * 0.7 + COALESCE(k.keyword_score, 0) * 0.3) > ${minSimilarity}
        ORDER BY combined_score DESC
        LIMIT ${limit}
      `;
    } else {
      // Vector only with filters
      return await prisma.$queryRaw`
        SELECT 
          id, name, description, brand, price, color,
          1 - (embedding <=> ${queryEmbedding}::vector) as similarity
        FROM products
        WHERE ${whereClause}
          AND 1 - (embedding <=> ${queryEmbedding}::vector) > ${minSimilarity}
        ORDER BY embedding <=> ${queryEmbedding}::vector
        LIMIT ${limit}
      `;
    }
  }
}

Usage Example

// Search with filters
const results = await AdvancedSearchService.searchProducts({
  query: "comfortable running shoes",
  filters: {
    brand: "Nike",
    minPrice: 50,
    maxPrice: 150,
    colors: ["red", "black"],
    inStock: true,
  },
  limit: 10,
  useHybrid: true,
});

Reranking for Better Results

After getting initial results from vector search, rerank them using additional signals:

interface RankingSignals {
  popularity?: number;      // View count, sales
  recency?: Date;           // Newer = better
  rating?: number;          // User ratings
  personalScore?: number;   // Personalized for user
}

export class RerankingService {
  /**
   * Rerank search results using multiple signals
   */
  static rerank<T extends { id: number; similarity: number }>(
    results: T[],
    signals: Map<number, RankingSignals>
  ): T[] {
    return results
      .map(result => {
        const signal = signals.get(result.id) || {};
        
        // Weighted scoring
        const vectorScore = result.similarity * 0.4;
        const popularityScore = (signal.popularity || 0) / 1000 * 0.2;
        const recencyScore = this.calculateRecencyScore(signal.recency) * 0.2;
        const ratingScore = (signal.rating || 0) / 5 * 0.1;
        const personalScore = (signal.personalScore || 0) * 0.1;
        
        const finalScore = vectorScore + popularityScore + recencyScore + ratingScore + personalScore;
        
        return { ...result, finalScore };
      })
      .sort((a, b) => b.finalScore - a.finalScore);
  }
  
  private static calculateRecencyScore(date?: Date): number {
    if (!date) return 0;
    
    const daysSince = (Date.now() - date.getTime()) / (1000 * 60 * 60 * 24);
    
    // Exponential decay (newer is better)
    return Math.exp(-daysSince / 30);
  }
}

// Usage
const searchResults = await searchProducts("wireless headphones");

// Get ranking signals
const signals = new Map<number, RankingSignals>();
for (const product of searchResults) {
  const stats = await getProductStats(product.id);
  signals.set(product.id, {
    popularity: stats.viewCount + stats.salesCount,
    recency: product.createdAt,
    rating: stats.averageRating,
  });
}

// Rerank
const reranked = RerankingService.rerank(searchResults, signals);

Multi-Vector Queries

Search across multiple vector columns (e.g., product name + description + reviews).

// Schema with multiple embeddings
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  description TEXT,
  
  -- Separate embeddings for different fields
  name_embedding VECTOR(1536),
  description_embedding VECTOR(1536),
  reviews_embedding VECTOR(1536)  -- Aggregated reviews
);

// Multi-vector search
export class MultiVectorSearch {
  static async search(query: string) {
    const queryVector = await EmbeddingService.getEmbedding(query);
    
    return await prisma.$queryRaw`
      SELECT 
        id,
        name,
        description,
        -- Weighted combination of similarities
        (
          (1 - (name_embedding <=> ${queryVector}::vector)) * 0.5 +
          (1 - (description_embedding <=> ${queryVector}::vector)) * 0.3 +
          (1 - (reviews_embedding <=> ${queryVector}::vector)) * 0.2
        ) as combined_similarity
      FROM products
      WHERE 
        name_embedding IS NOT NULL AND
        description_embedding IS NOT NULL
      ORDER BY combined_similarity DESC
      LIMIT 10
    `;
  }
}

Query Expansion

Expand user queries to find more relevant results:

export class QueryExpansionService {
  /**
   * Expand query with synonyms and related terms
   */
  static async expandQuery(query: string): Promise<string[]> {
    // Use LLM to generate query variations
    const response = await openai.chat.completions.create({
      model: 'gpt-4',
      messages: [
        {
          role: 'system',
          content: 'Generate 3 variations of the search query with synonyms and related terms. Return JSON array.',
        },
        {
          role: 'user',
          content: query,
        },
      ],
    });
    
    const variations = JSON.parse(response.choices[0].message.content);
    return [query, ...variations];
  }
  
  /**
   * Search with expanded queries
   */
  static async searchExpanded(query: string, limit: number = 10) {
    const queries = await this.expandQuery(query);
    const allResults = new Map();
    
    // Search for each variation
    for (const q of queries) {
      const results = await searchProducts(q, { limit: limit * 2 });
      
      for (const result of results) {
        if (!allResults.has(result.id)) {
          allResults.set(result.id, result);
        } else {
          // Boost score if found in multiple queries
          const existing = allResults.get(result.id);
          existing.similarity = Math.max(existing.similarity, result.similarity) * 1.1;
        }
      }
    }
    
    return Array.from(allResults.values())
      .sort((a, b) => b.similarity - a.similarity)
      .slice(0, limit);
  }
}

Faceted Search with Vectors

Combine faceted filtering with vector search:

interface Facet {
  field: string;
  values: Array<{ value: string; count: number }>;
}

export class FacetedSearchService {
  /**
   * Get facets for search results
   */
  static async searchWithFacets(query: string) {
    const queryVector = await EmbeddingService.getEmbedding(query);
    
    // Get search results
    const results = await prisma.$queryRaw`
      SELECT * FROM products
      WHERE 1 - (embedding <=> ${queryVector}::vector) > 0.6
      ORDER BY embedding <=> ${queryVector}::vector
      LIMIT 100
    `;
    
    // Calculate facets from results
    const facets: Facet[] = [
      await this.getFacet(results, 'category'),
      await this.getFacet(results, 'brand'),
      await this.getPriceFacet(results),
    ];
    
    return { results, facets };
  }
  
  private static async getFacet(results: any[], field: string): Promise<Facet> {
    const counts = new Map<string, number>();
    
    for (const result of results) {
      const value = result[field];
      if (value) {
        counts.set(value, (counts.get(value) || 0) + 1);
      }
    }
    
    const values = Array.from(counts.entries())
      .map(([value, count]) => ({ value, count }))
      .sort((a, b) => b.count - a.count);
    
    return { field, values };
  }
  
  private static async getPriceFacet(results: any[]): Promise<Facet> {
    const ranges = [
      { label: 'Under $50', min: 0, max: 50 },
      { label: '$50 - $100', min: 50, max: 100 },
      { label: '$100 - $200', min: 100, max: 200 },
      { label: 'Over $200', min: 200, max: Infinity },
    ];
    
    const values = ranges.map(range => {
      const count = results.filter(
        r => r.price >= range.min && r.price < range.max
      ).length;
      
      return { value: range.label, count };
    });
    
    return { field: 'price_range', values };
  }
}

Personalized Vector Search

Customize search results based on user preferences:

interface UserProfile {
  userId: string;
  preferredCategories: string[];
  viewHistory: number[];  // Product IDs
  purchaseHistory: number[];
}

export class PersonalizedSearchService {
  /**
   * Generate user preference embedding
   */
  static async getUserEmbedding(profile: UserProfile): Promise<number[]> {
    // Get embeddings of user's viewed/purchased products
    const products = await prisma.product.findMany({
      where: {
        id: { in: [...profile.viewHistory, ...profile.purchaseHistory] },
      },
      select: { embedding: true },
    });
    
    // Average embeddings to create user preference vector
    const embeddings = products.map(p => p.embedding as unknown as number[]);
    const userEmbedding = this.averageVectors(embeddings);
    
    return userEmbedding;
  }
  
  /**
   * Personalized search
   */
  static async personalizedSearch(
    query: string,
    profile: UserProfile,
    limit: number = 10
  ) {
    const queryVector = await EmbeddingService.getEmbedding(query);
    const userVector = await this.getUserEmbedding(profile);
    
    // Combine query similarity with user preference similarity
    const results = await prisma.$queryRaw`
      SELECT 
        id, name, description, price,
        (
          (1 - (embedding <=> ${queryVector}::vector)) * 0.7 +
          (1 - (embedding <=> ${userVector}::vector)) * 0.3
        ) as personalized_score
      FROM products
      WHERE embedding IS NOT NULL
      ORDER BY personalized_score DESC
      LIMIT ${limit}
    `;
    
    return results;
  }
  
  private static averageVectors(vectors: number[][]): number[] {
    if (vectors.length === 0) return [];
    
    const avgVector = new Array(vectors[0].length).fill(0);
    
    for (const vector of vectors) {
      for (let i = 0; i < vector.length; i++) {
        avgVector[i] += vector[i];
      }
    }
    
    return avgVector.map(v => v / vectors.length);
  }
}

Filtering Vector Results Post-Search

Sometimes it's better to filter after vector search:

export class PostFilterService {
  /**
   * Get broad vector results, then filter
   */
  static async searchThenFilter(
    query: string,
    filters: ProductFilters
  ) {
    const queryVector = await EmbeddingService.getEmbedding(query);
    
    // 1. Get broad vector results (top 100)
    const candidates = await prisma.$queryRaw<Product[]>`
      SELECT * FROM products
      WHERE embedding IS NOT NULL
      ORDER BY embedding <=> ${queryVector}::vector
      LIMIT 100
    `;
    
    // 2. Apply filters in-memory
    let filtered = candidates;
    
    if (filters.brand) {
      filtered = filtered.filter(p => p.brand === filters.brand);
    }
    
    if (filters.minPrice !== undefined) {
      filtered = filtered.filter(p => p.price >= filters.minPrice!);
    }
    
    if (filters.maxPrice !== undefined) {
      filtered = filtered.filter(p => p.price <= filters.maxPrice!);
    }
    
    if (filters.colors && filters.colors.length > 0) {
      filtered = filtered.filter(p => filters.colors!.includes(p.color));
    }
    
    // 3. Return top 10 after filtering
    return filtered.slice(0, 10);
  }
}

When to use post-filtering:

• Filters are very selective (would eliminate most vectors)

• You want maximum semantic relevance first

• Number of candidates is manageable

When to use pre-filtering:

• Filters significantly reduce candidate set

• Database indexes can accelerate filtering

• Memory constraints

Complete Advanced Search API

// src/routes/advanced-search.ts
import { Router, Request, Response } from 'express';
import { z } from 'zod';
import {
  AdvancedSearchService,
  RerankingService,
  FacetedSearchService,
  PersonalizedSearchService,
} from '../services/advanced-search.service';

const router = Router();

const AdvancedSearchSchema = z.object({
  query: z.string().min(1),
  filters: z.object({
    category: z.string().optional(),
    brand: z.string().optional(),
    minPrice: z.number().optional(),
    maxPrice: z.number().optional(),
    colors: z.array(z.string()).optional(),
    inStock: z.boolean().optional(),
    tags: z.array(z.string()).optional(),
  }).optional(),
  limit: z.number().min(1).max(100).default(20),
  minSimilarity: z.number().min(0).max(1).default(0.6),
  useHybrid: z.boolean().default(true),
  useReranking: z.boolean().default(false),
  useFacets: z.boolean().default(false),
  userId: z.string().optional(),
});

router.post('/search/advanced', async (req: Request, res: Response) => {
  try {
    const input = AdvancedSearchSchema.parse(req.body);
    
    // Get search results
    let results = await AdvancedSearchService.searchProducts(input);
    
    // Apply reranking if requested
    if (input.useReranking) {
      const signals = new Map();
      // TODO: Fetch ranking signals
      results = RerankingService.rerank(results, signals);
    }
    
    // Get facets if requested
    let facets = undefined;
    if (input.useFacets) {
      const facetResult = await FacetedSearchService.searchWithFacets(input.query);
      facets = facetResult.facets;
    }
    
    res.json({
      success: true,
      query: input.query,
      count: results.length,
      results,
      facets,
    });
  } catch (error: any) {
    res.status(400).json({
      success: false,
      error: error.message,
    });
  }
});

export default router;

What's Next

In this article, you learned:

• ✅ Hybrid search combining vectors + filters + keywords

• ✅ Reranking with multiple signals

• ✅ Multi-vector queries

• ✅ Query expansion for better recall

• ✅ Faceted search

• ✅ Personalized search

• ✅ When to filter before vs after vector search

Next: Production best practices including index optimization, caching strategies, and monitoring.

---

← Part 4: TypeScript Implementation | Part 6: Performance Optimization →