1"use strict";(self.webpackChunkwebsite=self.webpackChunkwebsite||[]).push([["112611"],{191502(e,r,n){n.r(r),n.d(r,{metadata:()=>s,default:()=>h,frontMatter:()=>l,contentTitle:()=>d,toc:()=>a,assets:()=>t});var s=JSON.parse('{"id":"reference/sql/search","title":"Search in SQL","description":"Reference for search functions and filtering in Spice SQL.","source":"@site/versioned_docs/version-2.0.x/reference/sql/search.md","sourceDirName":"reference/sql","slug":"/reference/sql/search","permalink":"/docs/v2.0/reference/sql/search","draft":false,"unlisted":false,"editUrl":"https://github.com/spiceai/docs/edit/trunk/website/versioned_docs/version-2.0.x/reference/sql/search.md","tags":[],"version":"2.0.x","sidebarPosition":20,"frontMatter":{"title":"Search in SQL","sidebar_label":"Search","description":"Reference for search functions and filtering in Spice SQL.","sidebar_position":20},"sidebar":"docs","previous":{"title":"JSON","permalink":"/docs/v2.0/reference/sql/json"},"next":{"title":"DML","permalink":"/docs/v2.0/reference/sql/dml"}}'),i=n(474848),c=n(28453);let l={title:"Search in SQL",sidebar_label:"Search",description:"Reference for search functions and filtering in Spice SQL.",sidebar_position:20},d,t={},a=[{value:"Table of Contents",id:"table-of-contents",level:2},{value:"Vector Search (<code>vector_search</code>)",id:"vector-search-vector_search",level:2},{value:"Usage",id:"usage",level:3},{value:"Filter Pushdown",id:"filter-pushdown",level:4},{value:"Example",id:"example",level:4},{value:"Multi-Query (Late-Interaction) Form",id:"multi-query-late-interaction-form",level:3},{value:"Full-Text Search (<code>text_search</code>)",id:"full-text-search-text_search",level:2},{value:"Usage",id:"usage-1",level:3},{value:"Example",id:"example-1",level:4},{value:"Reciprocal Rank Fusion (<code>rrf</code>)",id:"reciprocal-rank-fusion-rrf",level:2},{value:"Usage",id:"usage-2",level:3},{value:"Filter Pushdown",id:"filter-pushdown-1",level:4},{value:"Examples",id:"examples",level:4},{value:"Reranking (<code>rerank</code>)",id:"reranking-rerank",level:2},{value:"Usage",id:"usage-3",level:3},{value:"Candidate Pool",id:"candidate-pool",level:4},{value:"Query Auto-Propagation",id:"query-auto-propagation",level:4},{value:"Examples",id:"examples-1",level:4},{value:"Lexical Search: LIKE, =, and Regex",id:"lexical-search-like--and-regex",level:2},{value:"LIKE (Pattern Matching)",id:"like-pattern-matching",level:3},{value:"= (Keyword/Exact Match)",id:"-keywordexact-match",level:3},{value:"Regex Filtering",id:"regex-filtering",level:3},{value:"Example",id:"example-2",level:4}];function o(e){let r={a:"a",admonition:"admonition",code:"code",em:"em",h2:"h2",h3:"h3",h4:"h4",hr:"hr",li:"li",p:"p",pre:"pre",strong:"strong",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",ul:"ul",...(0,c.R)(),...e.components};return(0,i.jsxs)(i.Fragment,{children:[(0,i.jsx)(r.p,{children:"This section documents search capabilities in Spice SQL, including vector search, full-text search, and lexical filtering methods. These features help retrieve relevant data using semantic similarity, keyword matching, and pattern-based filtering."}),"\n",(0,i.jsx)(r.h2,{id:"table-of-contents",children:"Table of Contents"}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#table-of-contents",children:"Table of Contents"})}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsxs)(r.a,{href:"#vector-search-vector_search",children:["Vector Search (",(0,i.jsx)(r.code,{children:"vector_search"}),")"]}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.a,{href:"#usage",children:"Usage"}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#example",children:"Example"})}),"\n"]}),"\n"]}),"\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#multi-query-late-interaction-form",children:"Multi-Query (Late-Interaction) Form"})}),"\n"]}),"\n"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsxs)(r.a,{href:"#full-text-search-text_search",children:["Full-Text Search (",(0,i.jsx)(r.code,{children:"text_search"}),")"]}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.a,{href:"#usage-1",children:"Usage"}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#example-1",children:"Example"})}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsxs)(r.a,{href:"#reciprocal-rank-fusion-rrf",children:["Reciprocal Rank Fu
1sion (",(0,i.jsx)(r.code,{children:"rrf"}),")"]}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.a,{href:"#usage-2",children:"Usage"}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#examples",children:"Examples"})}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n",(0,i.jsx)(r.li,{children:(0,i.jsxs)(r.a,{href:"#reranking-rerank",children:["Reranking (",(0,i.jsx)(r.code,{children:"rerank"}),")"]})}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.a,{href:"#lexical-search-like--and-regex",children:"Lexical Search: LIKE, =, and Regex"}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#like-pattern-matching",children:"LIKE (Pattern Matching)"})}),"\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#-keywordexact-match",children:"= (Keyword/Exact Match)"})}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.a,{href:"#regex-filtering",children:"Regex Filtering"}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsx)(r.li,{children:(0,i.jsx)(r.a,{href:"#example-2",children:"Example"})}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n"]}),"\n",(0,i.jsx)(r.hr,{}),"\n",(0,i.jsxs)(r.h2,{id:"vector-search-vector_search",children:["Vector Search (",(0,i.jsx)(r.code,{children:"vector_search"}),")"]}),"\n",(0,i.jsx)(r.p,{children:"Vector search retrieves records by semantic similarity using embeddings. It is ideal for finding related content even when exact keywords differ."}),"\n",(0,i.jsx)(r.h3,{id:"usage",children:"Usage"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT id, score\nFROM vector_search(table, 'search query')\nORDER BY score DESC\nLIMIT 5;\n"})}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"table"}),": Dataset name (required)"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"query"}),": Search text, or an array of strings for ",(0,i.jsx)(r.a,{href:"#multi-query-late-interaction-form",children:"multi-query"})," search (required)"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"column"}),": Column name (optional if only one embedding column; required when the table has multiple embedded columns)"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"limit"}),": Maximum results (optional). When omitted, the engine-defined maximum is used."]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"include_score"}),": Include relevance scores (optional, default ",(0,i.jsx)(r.code,{children:"TRUE"}),")"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"distance_metric"}),": Similarity metric used to rank candidate vectors (optional, named argument). Supported values: ",(0,i.jsx)(r.code,{children:"'cosine'"})," (default) and ",(0,i.jsx)(r.code,{children:"'l2'"})," (negated Euclidean distance). ",(0,i.jsx)(r.code,{children:"'dot'"})," is parsed but not yet wired through the scan path."]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"rank_weight"}),": Per-query ranking weight (optional, named argument). Only meaningful when ",(0,i.jsx)(r.code,{children:"vector_search"})," is passed as a subquery to ",(0,i.jsx)(r.a,{href:"#reciprocal-rank-fusion-rrf",children:(0,i.jsx)(r.code,{children:"rrf"})}),"."]}),"\n"]}),"\n",(0,i.jsx)(r.h4,{id:"filter-pushdown",children:"Filter Pushdown"}),"\n",(0,i.jsxs)(r.p,{children:[(0,i.jsx)(r.code,{children:"WHERE"})," predicates on base table columns (e.g., ",(0,i.jsx)(r.code,{children:"created_at"}),", ",(0,i.jsx)(r.code,{children:"product_category"}),") are pushed down as ",(0,i.jsx)(r.strong,{children:"pre-filters"})," \u2014 they are applied before the similarity ranking, so only matching rows are scored and returned. This means results reflect the top-K ",(0,i.jsx)(r.em,{children:"within the filtered set"}),", not the top-K of the entire table filtered afterward."]}),"\n",(0,i.jsxs)(r.p,{children:["Predicates on computed columns like ",(0,i.jsx)(r.code,{children:"score"})," are applied as post-filters after ranking."]}),"\n",(0,i.jsx)(r.h4,{id:"example",children:"Example"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"-- Filters on created_at are pushed down before ranking\nSELECT review_id, rating, customer_id, body, score\nFROM vector_search(reviews, 'issues with same day shipping', 1500)\nWHERE created_at >= to_unixtime(now() - INTERVAL '7 days')\nORDER BY score DESC\nLIMIT 2;\n"})}),"\n",(0,i.jsxs)(r.p,{children:["To override the similarity metric, pass ",(0,i.jsx)(r.code,{children:"distance_metric"})," as a named argument:"]}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT id, body, score\nFROM vector_search(reviews, 'issues with shipping', distance_metric => 'l2')\nORDER BY score DESC\nLIMIT 10;\n"})}),"\n",(0,i.jsxs)(r.p,{children:["See ",(0,i.jsx)(r.a,{href:"../../features/search/vector-search",children:"Vector-Based Search"})," for configuration and advanced usage."]}),"\n",(0,i.jsx)(r.h3,{id:"multi-query-late-interaction-form",children:"Multi-Query (Late-Interaction) Form"}),"\n",(0,i.jsxs)(r.p,{children:["When the target column is a ",(0,i.jsx)(r.a,{href:"../../features/search/multi-vector",children:"multi-vector column"}),", ",(0,i.jsx)(r.code,{children:"vector_search"})," also accepts an array of query strings. Each query is embedded independently and the per-row score is ",(0,i.jsx)(r.code,{children:"\u03A3_q max_e cos(q, e)"})," \u2014 ColBERT-style late interaction. Passing an array to a scalar or chunked column returns an error. At most 32 query strings are accepted per call."]}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT product_id, name, score\nFROM vector_search(products, ['hiking', 'waterproof', 'lightweight'], tags)\nORDER BY score DESC\nLIMIT 10;\n"})}),"\n",(0,i.jsx)(r.hr,{}),"\n",(0,i.jsxs)(r.h2,{id:"full-text-search-text_search",children:["Full-Text Search (",(0,i.jsx)(r.code,{children:"text_search"}),")"]}),"\n",(0,i.jsx)(r.p,{children:"Full-text search uses BM25 scoring to retrieve records matching keywords in indexed columns."}),"\n",(0,i.jsx)(r.h3,{id:"usage-1",children:"Usage"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT id, score\nFROM text_search(table, 'search terms', col)\nORDER BY score DESC\nLIMIT 5;\n"})}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"table"}),": Dataset name (required)"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"query"}),": Keyword or phrase (required)"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"column"}),": Column to search (optional if the table has a single full-text index; required when multiple columns are indexed)"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"limit"}),": Maximum results (optional). Defaults to 1000, which is the maximum supported."]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"include_score"}),": Include relevance scores (optional, default ",(0,i.jsx)(r.code,{children:"TRUE"}),")"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"rank_weight"}),": Per-query ranking weight (optional, named argument). Only meaningful when ",(0,i.jsx)(r.code,{children:"text_search"})," is passed as a subquery to ",(0,i.jsx)(r.a,{href:"#reciprocal-rank-fusion-rrf",children:(0,i.jsx)(r.code,{children:"rrf"})}),"."]}),"\n"]}),"\n",(0,i.jsxs)(r.p,{children:["By default, ",(0,i.jsx)(r.code,{children:"text_search"})," retrieves up to 1000 results. To request fewer, specify a smaller ",(0,i.jsx)(r.code,{children:"limit"}),"."]}),"\n",(0,i.jsx)(r.h4,{id:"example-1",children:"Example"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT id, title, score\nFROM text_search(doc.pulls, 'search keywords', body)\nORDER BY score DESC\nLIMIT 5;\n"})}),"\n",(0,i.jsxs)(r.p,{children:["See ",(0,i.jsx)(r.a,{href:"../../features/search/full-text",children:"Full-Text Search"})," for configuration and details."]}),"\n",(0,i.jsx)(r.hr,{}),"\n",(0,i.jsxs)(r.h2,{id:"reciprocal-rank-fusion-rrf",children:["Reciprocal Rank Fu
1sion (",(0,i.jsx)(r.code,{children:"rrf"}),")"]}),"\n",(0,i.jsx)(r.p,{children:"Reciprocal Rank Fusion (RRF) combines results from multiple search queries to improve relevance by merging rankings from different search methods. Advanced features include per-query ranking weights, recency boosting, and flexible decay functions."}),"\n",(0,i.jsx)(r.h3,{id:"usage-2",children:"Usage"}),"\n",(0,i.jsxs)(r.p,{children:[(0,i.jsx)(r.code,{children:"rrf"})," is variadic and takes two or more search UDTF calls as arguments. Named parameters provide advanced control over ranking, recency, and fusion behavior."]}),"\n",(0,i.jsx)(r.admonition,{type:"info",children:(0,i.jsxs)(r.p,{children:["The ",(0,i.jsx)(r.code,{children:"rrf"})," function automatically adds a ",(0,i.jsx)(r.code,{children:"fused_score"})," column to the result set, which contains the combined relevance score from all input search queries. Results are sorted by ",(0,i.jsx)(r.code,{children:"fused_score DESC"})," by default when no explicit ",(0,i.jsx)(r.code,{children:"ORDER BY"})," clause is specified."]})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT id, content, fused_score\nFROM rrf(\n vector_search(table, 'search query', rank_weight => 20),\n text_search(table, 'search terms', column),\n join_key => 'id', -- explicit join key for performance\n k => 60.0 -- smoothing parameter\n)\nORDER BY fused_score DESC\nLIMIT 10;\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Arguments:"})}),"\n",(0,i.jsxs)(r.p,{children:["Note that ",(0,i.jsx)(r.code,{children:"rank_weight"})," is specified as the last argument to either a ",(0,i.jsx)(r.code,{children:"text_search"})," or ",(0,i.jsx)(r.code,{children:"vector_search"})," UDTF call (as shown above). All other arguments can be specified in any order after the search calls (within an ",(0,i.jsx)(r.code,{children:"rrf"})," invocation)."]}),"\n",(0,i.jsxs)(r.table,{children:[(0,i.jsx)(r.thead,{children:(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.th,{children:"Parameter"}),(0,i.jsx)(r.th,{children:"Type"}),(0,i.jsx)(r.th,{children:"Required"}),(0,i.jsx)(r.th,{children:"Description"})]})}),(0,i.jsxs)(r.tbody,{children:[(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"query_1"})}),(0,i.jsx)(r.td,{children:"Search UDTF call"}),(0,i.jsx)(r.td,{children:"Yes"}),(0,i.jsxs)(r.td,{children:["First search query (e.g., ",(0,i.jsx)(r.code,{children:"vector_search"}),", ",(0,i.jsx)(r.code,{children:"text_search"}),")"]})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"query_2"})}),(0,i.jsx)(r.td,{children:"Search UDTF call"}),(0,i.jsx)(r.td,{children:"Yes"}),(0,i.jsxs)(r.td,{children:["Second search query. ",(0,i.jsx)(r.code,{children:"rrf"})," requires at least two subqueries."]})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"..."})}),(0,i.jsx)(r.td,{children:"Search UDTF call"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Additional search queries (variadic)"})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"join_key"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Column name to use for joining subquery results. If omitted, the primary key is inferred from the underlying tables; otherwise rows are auto-hashed."})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"k"})}),(0,i.jsx)(r.td,{children:"Float"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Smoothing parameter for RRF scoring (default: 60.0)"})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"limit"})}),(0,i.jsx)(r.td,{children:"Integer"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Upper bound on the fused result set. Also propagated as a default limit to any nested search subquery that does not specify its own."})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"time_column"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Column name containing timestamps for recency boosting"})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"recency_decay"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Decay function: 'linear' or 'exponential' (default: 'exponential')"})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"decay_constant"})}),(0,i.jsx)(r.td,{children:"Float"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Decay rate for exponential decay (default: 0.01)"})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"decay_scale_secs"})}),(0,i.jsx)(r.td,{children:"Float"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Time scale in seconds for decay (default: 86400)"})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"decay_window_secs"})}),(0,i.jsx)(r.td,{children:"Float"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Window size for linear decay in seconds (default: 86400)"})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"rank_weight"})}),(0,i.jsx)(r.td,{children:"Float"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsxs)(r.td,{children:["Per-query ranking weight (",(0,i.jsx)(r.strong,{children:"specified within the individual search subquery call"}),")"]})]})]})]}),"\n",(0,i.jsx)(r.h4,{id:"filter-pushdown-1",children:"Filter Pushdown"}),"\n",(0,i.jsxs)(r.p,{children:[(0,i.jsx)(r.code,{children:"WHERE"})," predicates on base table columns (e.g., ",(0,i.jsx)(r.code,{children:"review_date"}),", ",(0,i.jsx)(r.code,{children:"product_category"}),") are pushed down into each nested search subquery as ",(0,i.jsx)(r.strong,{children:"pre-filters"})," \u2014 they are applied before ranking and fusion, so each subquery only considers matching rows. This means the fused results reflect the top-K ",(0,i.jsx)(r.em,{children:"within the filtered set"}),", not a post-filtered slice of unfiltered rankings."]}),"\n",(0,i.jsxs)(r.p,{children:["Predicates on computed columns like ",(0,i.jsx)(r.code,{children:"fused_score"})," are applied as post-filters after fusion."]}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"-- review_date and product_category are pushed into each vector_search before ranking\nSELECT review_id, review_headline\nFROM rrf(\n vector_search(amazon_reviews, 'cannot exit the app', rank_weight => 20),\n vector_search(amazon_reviews, 'app not working', rank_weight => 10),\n join_key => 'review_id',\n k => 60.0\n)\nWHERE review_date > '2015-06-15' AND product_category = 'Mobile_Apps'\nLIMIT 10;\n"})}),"\n",(0,i.jsx)(r.h4,{id:"examples",children:"Examples"}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Basic Hybrid Search:"})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"-- Combine vector and text search for enhanced relevance\nSELECT id, title, content, fused_score\nFROM rrf(\n vector_search(documents, 'machine learning algorithms'),\n text_search(documents, 'neural networks deep learning', content),\n join_key => 'id' -- explicit join key for performance\n)\nWHERE fused_score > 0.01\nORDER BY fused_score DESC\nLIMIT 5;\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Weighted Ranking:"})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"-- Boost semantic search over exact text matching\nSELECT fused_score, title, content\nFROM rrf(\n text_search(posts, 'artificial intelligence', rank_weight => 50.0),\n vector_search(posts, 'AI machine learning', rank_weight => 200.0)\n)\nORDER BY fused_score DESC\nLIMIT 10;\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Recency-Boosted Search:"})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"-- Exponential decay favoring recent content\nSELECT fused_score, title, created_at\nFROM rrf(\n text_search(news, 'breaking news'),\n vector_search(news, 'latest updates'),\n time_column => 'created_at',\n recency_decay => 'exponential',\n decay_constant => 0.05,\n decay_scale_secs => 3600 -- 1 hour scale\n)\nORDER BY fused_score DESC\nLIMIT 10;\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Linear Decay:"})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"-- Linear decay over 24 hours\nSELECT fused_score, content\nFROM rrf(\n text_search(posts, 'trending'),\n vector_search(posts, 'viral popular'),\n time_column => 'created_at',\n recency_decay => 'linear',\n decay_window_secs =>
1 86400\n)\nORDER BY fused_score DESC;\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"How RRF works:"})}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsx)(r.li,{children:"Each input query is ranked independently by score"}),"\n",(0,i.jsxs)(r.li,{children:["Rankings are combined using the formula: ",(0,i.jsx)(r.code,{children:"RRF Score = \u03A3(rank_weight / (k + rank))"})]}),"\n",(0,i.jsx)(r.li,{children:"Documents appearing in multiple result sets receive higher scores"}),"\n",(0,i.jsxs)(r.li,{children:["The ",(0,i.jsx)(r.code,{children:"k"})," parameter controls ranking sensitivity (lower = more sensitive to rank position)"]}),"\n"]}),"\n",(0,i.jsxs)(r.p,{children:[(0,i.jsx)(r.strong,{children:"Advanced query tuning"}),":"]}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.strong,{children:"Rank weighting"}),": Individual queries can be weighted using ",(0,i.jsx)(r.code,{children:"rank_weight"})," parameter"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.strong,{children:"Recency boosting"}),": When ",(0,i.jsx)(r.code,{children:"time_column"})," is specified, scores are multiplied by a decay factor","\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.strong,{children:"Exponential decay"}),": ",(0,i.jsx)(r.code,{children:"e^(-decay_constant * age_in_units)"})," where age is in ",(0,i.jsx)(r.code,{children:"decay_scale_secs"})]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.strong,{children:"Linear decay"}),": ",(0,i.jsx)(r.code,{children:"max(0, 1 - (age_in_units / decay_window_secs))"})]}),"\n"]}),"\n"]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.strong,{children:"Auto-join"}),": When no ",(0,i.jsx)(r.code,{children:"join_key"})," is specified, ",(0,i.jsx)(r.code,{children:"rrf"})," infers the primary key from the underlying tables; if none is available, rows are joined by an auto-generated row identifier"]}),"\n"]}),"\n",(0,i.jsx)(r.hr,{}),"\n",(0,i.jsxs)(r.h2,{id:"reranking-rerank",children:["Reranking (",(0,i.jsx)(r.code,{children:"rerank"}),")"]}),"\n",(0,i.jsxs)(r.p,{children:["Reranking reorders candidate results using a dedicated reranker model or an LLM-as-reranker for improved relevance. The input can be any search UDTF (",(0,i.jsx)(r.code,{children:"vector_search"}),", ",(0,i.jsx)(r.code,{children:"text_search"}),", ",(0,i.jsx)(r.code,{children:"rrf"}),") or a plain table."]}),"\n",(0,i.jsx)(r.h3,{id:"usage-3",children:"Usage"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT *\nFROM rerank(\n <input>,\n document => 'column_name',\n model => 'reranker_name',\n limit => 10\n)\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Arguments:"})}),"\n",(0,i.jsxs)(r.table,{children:[(0,i.jsx)(r.thead,{children:(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.th,{children:"Parameter"}),(0,i.jsx)(r.th,{children:"Type"}),(0,i.jsx)(r.th,{children:"Required"}),(0,i.jsx)(r.th,{children:"Description"})]})}),(0,i.jsxs)(r.tbody,{children:[(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"input"})}),(0,i.jsx)(r.td,{children:"Table or UDTF"}),(0,i.jsx)(r.td,{children:"Yes"}),(0,i.jsxs)(r.td,{children:["Input rows to rerank. Can be a search UDTF call (",(0,i.jsx)(r.code,{children:"vector_search"}),", ",(0,i.jsx)(r.code,{children:"text_search"}),", ",(0,i.jsx)(r.code,{children:"rrf"}),") or a table name."]})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"model"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsxs)(r.td,{children:["Name of a registered reranker or chat model. Omit it only when exactly one reranker or chat model is registered, in which case that one is used. With more than one registered, the query fails and ",(0,i.jsx)(r.code,{children:"model"})," chooses between them. With none registered, the query fails whether or not ",(0,i.jsx)(r.code,{children:"model"})," is given \u2014 no name can resolve until a reranker or chat model is added to the Spicepod."]})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"document"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"Yes"}),(0,i.jsx)(r.td,{children:"Column containing the text to send to the reranker for scoring."})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"query"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsx)(r.td,{children:"Query string for relevance scoring. Auto-extracted from nested search UDTFs when omitted; required for bare-table inputs."})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"limit"})}),(0,i.jsx)(r.td,{children:"Integer"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsxs)(r.td,{children:["Maximum number of rows returned. It caps the ",(0,i.jsx)(r.strong,{children:"output"})," only and never shrinks the candidate pool, so every candidate is still scored by the reranker \u2014 narrow the inner search's own ",(0,i.jsx)(r.code,{children:"limit"})," to reduce reranker calls."]})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"strategy"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsxs)(r.td,{children:["LLM reranking strategy: ",(0,i.jsx)(r.code,{children:"'listwise'"})," (default) or ",(0,i.jsx)(r.code,{children:"'pointwise'"}),". Only applies when the model resolves to a chat model."]})]}),(0,i.jsxs)(r.tr,{children:[(0,i.jsx)(r.td,{children:(0,i.jsx)(r.code,{children:"prompt_template"})}),(0,i.jsx)(r.td,{children:"String"}),(0,i.jsx)(r.td,{children:"No"}),(0,i.jsxs)(r.td,{children:["Custom prompt template for LLM-as-reranker. Use ",(0,i.jsx)(r.code,{children:"{query}"})," and ",(0,i.jsx)(r.code,{children:"{document}"})," placeholders. Only applies when the model resolves to a chat model."]})]})]})]}),"\n",(0,i.jsx)(r.h4,{id:"candidate-pool",children:"Candidate Pool"}),"\n",(0,i.jsxs)(r.p,{children:[(0,i.jsx)(r.code,{children:"rerank"})," scores every row its input produces, so the input decides the reranker cost:"]}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.strong,{children:"Nested search UDTF"})," \u2014 the inner UDTF's own ",(0,i.jsx)(r.code,{children:"limit"})," bounds the candidate pool. ",(0,i.jsx)(r.code,{children:"rerank"}),"'s ",(0,i.jsx)(r.code,{children:"limit"})," is not pushed into it, which is what lets the recall-then-rerank pattern pass a large inner ",(0,i.jsx)(r.code,{children:"limit"})," and a small outer one."]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.strong,{children:"Bare table"})," \u2014 there is no inner limit, so a fixed ceiling of ",(0,i.jsx)(r.strong,{children:"1000 candidate rows"})," is applied to the scan. A larger table is silently truncated to the first 1000 rows reaching the reranker; use a nested search UDTF, or a subquery with its own filter, when the table is bigger than that."]}),"\n"]}),"\n",(0,i.jsx)(r.h4,{id:"query-auto-propagation",children:"Query Auto-Propagation"}),"\n",(0,i.jsxs)(r.p,{children:["When the input is a search UDTF (",(0,i.jsx)(r.code,{children:"vector_search"}),", ",(0,i.jsx)(r.code,{children:"text_search"}),", or ",(0,i.jsx)(r.code,{children:"rrf"})," wrapping search UDTFs), the query string is automatically extracted from the nested call. Single-string, ",(0,i.jsx)(r.code,{children:"make_array(...)"}),", and ",(0,i.jsx)(r.code,{children:"ARRAY[...]"}
1)," query forms are all supported. For multi-query inputs, the first query string is used."]}),"\n",(0,i.jsxs)(r.p,{children:["For bare-table inputs, ",(0,i.jsx)(r.code,{children:"query"})," must be provided explicitly."]}),"\n",(0,i.jsx)(r.h4,{id:"examples-1",children:"Examples"}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Rerank hybrid search results:"})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT * FROM rerank(\n rrf(\n vector_search(docs, 'delta lake time travel', limit => 50),\n text_search(docs, 'delta lake time travel', limit => 50)\n ),\n document => 'content',\n model => 'cohere_rr',\n limit => 10\n);\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"Rerank a plain table with an explicit query:"})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT * FROM rerank(\n tickets,\n query => 'auth failures',\n document => 'body',\n model => 'voyage_rr',\n limit => 5\n);\n"})}),"\n",(0,i.jsx)(r.p,{children:(0,i.jsx)(r.strong,{children:"LLM-as-reranker with custom prompt:"})}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT * FROM rerank(\n vector_search(kb, 'onboarding checklist', limit => 40),\n document => 'content',\n model => 'gpt_mini',\n strategy => 'pointwise',\n prompt_template => 'Rate 0-1: is this useful for a new hire?\\nQuery: {query}\\nDoc: {document}',\n limit => 10\n);\n"})}),"\n",(0,i.jsxs)(r.p,{children:["See ",(0,i.jsx)(r.a,{href:"../../features/search/rerank",children:"Reranking"})," for configuration, provider setup, and additional examples."]}),"\n",(0,i.jsx)(r.hr,{}),"\n",(0,i.jsx)(r.h2,{id:"lexical-search-like--and-regex",children:"Lexical Search: LIKE, =, and Regex"}),"\n",(0,i.jsx)(r.p,{children:"Spice SQL supports traditional filtering for exact and pattern-based matches:"}),"\n",(0,i.jsx)(r.h3,{id:"like-pattern-matching",children:"LIKE (Pattern Matching)"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT * FROM my_table WHERE column LIKE '%substring%';\n"})}),"\n",(0,i.jsxs)(r.ul,{children:["\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"%"})," matches any sequence of characters."]}),"\n",(0,i.jsxs)(r.li,{children:[(0,i.jsx)(r.code,{children:"_"})," matches a single character."]}),"\n"]}),"\n",(0,i.jsx)(r.h3,{id:"-keywordexact-match",children:"= (Keyword/Exact Match)"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT * FROM my_table WHERE column = 'exact value';\n"})}),"\n",(0,i.jsx)(r.p,{children:"Returns rows where the column exactly matches the value."}),"\n",(0,i.jsx)(r.h3,{id:"regex-filtering",children:"Regex Filtering"}),"\n",(0,i.jsxs)(r.p,{children:["Spice SQL supports the PostgreSQL regex operators ",(0,i.jsx)(r.code,{children:"~"})," (match), ",(0,i.jsx)(r.code,{children:"~*"})," (case-insensitive match), ",(0,i.jsx)(r.code,{children:"!~"})," (not match), and ",(0,i.jsx)(r.code,{children:"!~*"})," (case-insensitive not match) \u2014 see ",(0,i.jsx)(r.a,{href:"./operators#op_re_match",children:"Operators"}),". Alternatively, use scalar functions such as ",(0,i.jsx)(r.code,{children:"regexp_like"}),", ",(0,i.jsx)(r.code,{children:"regexp_match"}),", and ",(0,i.jsx)(r.code,{children:"regexp_replace"}),". For details and examples, see the ",(0,i.jsx)(r.a,{href:"./scalar_functions#regular-expression-functions",children:"Scalar Functions documentation"}),"."]}),"\n",(0,i.jsx)(r.h4,{id:"example-2",children:"Example"}),"\n",(0,i.jsx)(r.pre,{children:(0,i.jsx)(r.code,{className:"language-sql",children:"SELECT * FROM my_table WHERE column ~ '^spice.*ai$';\n-- Or, equivalently:\nSELECT * FROM my_table WHERE regexp_like(column, '^spice.*ai$');\n"})}),"\n",(0,i.jsx)(r.hr,{}),"\n",(0,i.jsxs)(r.p,{children:["For more on hybrid and advanced search, see ",(0,i.jsx)(r.a,{href:"../../features/search",children:"Search Functionality"})," and ",(0,i.jsx)(r.a,{href:"../../features/search/vector-search",children:"Vector-Based Search"})]})]})}function h(e={}){let{wrapper:r}={...(0,c.R)(),...e.components};return r?(0,i.jsx)(r,{...e,children:(0,i.jsx)(o,{...e})}):o(e)}},28453(e,r,n){n.d(r,{R:()=>l,x:()=>d});var s=n(296540);let i={},c=s.createContext(i);function l(e){let r=s.useContext(c);return s.useMemo(function(){return"function"==typeof e?e(r):{...r,...e}},[r,e])}function d(e){let r;return r=e.disableParentContext?"function"==typeof e.components?e.components(i):e.components||i:l(e.components),s.createElement(c.Provider,{value:r},e.children)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.