Run one read-only SQL query in a DuckDB dialect with LLM semantic functions over web search results and result sets.
Use it whenever the task needs web data (current facts, prices, rankings, anything past your cutoff) and a structured, table-like output.
Standard SQL syntax is supported, including joins, UNION ALL, UNNEST(list_column), USING SAMPLE. The additional semantic functions:
- SEM_EXTRACT(column, 'field description', name := 'field description', ..., evidence := 'per_field'): extracts the value per row; null when the text does not state it. Named arguments return one object per row; the positional description is then optional and scopes the object ('an electric car model'). evidence := true adds columns with verbatim quotes; with named fields they sit under an "*_evidence" key inside the object.
- SEM_EXTRACT_ALL(column, 'what one value/entity is', name := 'field description', ..., evidence := 'per_field'): like SEM_EXTRACT but returns ALL matches as a list, null when none.
- SEM_MATCH(column, 'predicate'): LLM true/false per row; the semantic filter for WHERE.
- SEM_SCORE(column, 'query'): embedding relevance, use in ORDER BY. Only rank by something more specific than the search queries — search results are already ranked by those.
- SEM_NORM(column, 'clustering guidance'): canonical key merging equivalent values ('Google LLC', 'GOOGL' → 'Google'); use in GROUP BY and repeat the call there, never a bare alias. The guidance string is optional and says what to merge or keep apart for this column, for instance: SEM_NORM(company, 'merge a subsidiary into its parent').
And the additional search engine functions:
- WEB_SEARCH('query one', 'query two', ..., published_after := '1d') is a table function: merged, URL-deduplicated pages from all queries become rows with url, title, content, published_at (UTC TIMESTAMP or DATE; null when none was extracted), and query.
- WEB_FETCH('https://a.com/page', ...): one row per fetched URL with url, title, content, published_at. Use it to read specific pages with known or previously collected URLs.
SEM_EXTRACT and SEM_EXTRACT_ALL rules:
1. Format hints belong in field descriptions ('date, YYYY-MM-DD').
2. Avoid example answers in field descriptions: the extractor copies an example's wording (article, prefix, granularity) into every value.
3. Ask for the value by itself ('frame rate, fps'). Respect the original user question's formulation.
4. Evidence is a reserved argument: pass it at most once, as true, false, or 'per_field' (prefer 'per_field' when a call has several fields).
5. Never declare a field named "evidence" or a field that asks for supporting quotes, because only the evidence argument's quotes are verified against the page.
6. Extraction does not challenge the source's data! A single page may be outdated or the wrong edition or variant. Cross-check precise values (statistics, rankings, dates) across independent pages. Prefer pages whose date, edition, and variant match the question. Spot-check evidence quotes to catch values from the wrong table row, column, or year.
7. Before you extract, pin each requested column down to one exact definition: which year (announced, signed, or in effect), net or gross, which ID system, city or province. Put that definition in the extraction field description.
WEB_SEARCH rules:
1. Give at least 6 maximally diverse queries: vary terminology, source type, and angle as in the examples.
2. Time-scope with published_after/published_before ('2h', '1d', '2w', '1mo', or '2026-07-01'), not dates in the query text. Any date filter (argument or WHERE on published_at) drops undated pages (forums, filings), so filter by date only when recency truly matters.
General rules:
1. Every query must read FROM a result set id, WEB_SEARCH(...), or WEB_FETCH(...) — no other tables, no FROM-less SELECTs. Every output is saved as a result set keeping only the projected columns — reference it by id in FROM.
2. Never SELECT full content: it dumps whole pages into your context and is rejected, bare or via *. Pull what you need with SEM_EXTRACT / SEM_EXTRACT_ALL instead of reading pages. If you really need an exact read, use bounded windows like substr(content, 1, 10000) AS content_head, or a regexp probe. * EXCLUDE (content) keeps the other columns.
3. Outputs come back in full; ones too large for one response fall back to a 3-row preview with a note. Set show_preview=true when you only need the output's shape, not its rows.
4. On an error, read it and fix the query. 0 rows is not an error: widen the search, or re-query the input result set by id instead of re-searching.
5. Write SQL with line breaks: each clause starts a new line, and when a clause has multiple columns or arguments, put each on its own line. Queries should also be human-readable.
=====
Example 1: core query:
```sql
SELECT url, UNNEST(
SEM_EXTRACT_ALL( -- default when exploring; plain SEM_EXTRACT would keep one car per page
content,
'a production electric car model with a stated driving range',
model := 'manufacturer and model name',
range_km := 'driving range in km, number only',
price_usd := 'price in USD, number only; NULL unless stated in USD'
),
recursive := true -- one row per car, one column per field
)
FROM WEB_SEARCH(
'electric car longest range comparison test',
'EV real world range test results highway',
'electric sedan SUV range specs list',
'cheapest long range electric car',
'site:reddit.com EV actual range vs claimed', -- "site:" operators are supported
'new EV models range price announced'
)
WHERE SEM_MATCH(content, 'states the driving range of a specific electric car model')
```
=====
Example 2: advanced query with cross-page aggregation and evidence:
```sql
SELECT SEM_NORM(jacket) AS jacket, COUNT(DISTINCT url) AS mentions,
ANY_VALUE(fill) AS fill, ANY_VALUE(weight) AS weight,
MODE(msrp) AS msrp, ANY_VALUE(verdict) AS verdict,
list_filter(LIST({m: msrp, e: msrp_evidence}), x -> x.m = MODE(msrp))[1].e
AS msrp_evidence -- quotes paired with the modal msrp, not an arbitrary row's
FROM (
SELECT url, UNNEST(
SEM_EXTRACT_ALL(
content,
'insulated expedition down jacket or parka for high-altitude (6000-8000m) mountaineering',
jacket := 'brand and model name',
fill := 'down fill power and fill weight, FP and grams',
weight := 'total jacket weight, grams',
msrp := 'current retail price in USD, number only; NULL unless stated in USD',
verdict := 'what altitude/conditions the page says it suits, plus praise or criticism',
evidence := 'per_field' -- prefer with fields: each field carries its own verbatim quotes
),
recursive := true
)
FROM WEB_SEARCH(
'best expedition down jacket 8000m 7000m peaks review',
'Rab Expedition 8000 vs Mountain Equipment Redline jacket',
'down suit or jacket for Aconcagua Denali 7000m what to wear',
'The North Face Himalayan parka review high altitude',
'lightweight expedition parka Khan Tengri Pik Lenin Muztagh Ata gear list',
'warmest down jacket comparison fill weight expedition grade',
'site:reddit.com down jacket 8000m disappointed cold'
)
WHERE SEM_MATCH(
content,
'discusses a specific insulated jacket or parka suitable for high-altitude mountaineering'
)
)
GROUP BY SEM_NORM(jacket) ORDER BY mentions DESC
LIMIT 50 -- top-k question; omit LIMIT when listing all entities
```
=====
Example 3: a follow-up, one search per jacket:
```sql
SELECT jacket, price, price_evidence, url,
TRY_CAST(priced_at AS DATE) AS priced_at -- extracted dates stay VARCHAR: TRY_CAST before comparing or sorting
FROM (
SELECT jacket, url,
SEM_EXTRACT(content, 'current retail price in USD, number only; NULL unless stated in USD',
evidence := true) AS price,
SEM_EXTRACT(content, 'date of the price quote, YYYY-MM-DD') AS priced_at
FROM (
SELECT jacket, UNNEST(WEB_SEARCH(jacket || ' current price'), recursive := true)
FROM ra1b2c3d4e5f -- the previous output — reuse saved sets where possible
)
)
WHERE price IS NOT NULL
ORDER BY jacket, price -- differing prices for one jacket sit adjacent
```
=====
Use semantic functions generously! Prefer them over manual content reading.
select
View in the ChatGPT registry