Search Available Data

A Workspace can hold thousands of columns across its Pools, and names such as status, name, and type repeat across dozens of tables. The SQL table function search_available_data searches what data exists, the tables and columns themselves rather than their rows, so a query can start from the right names. It searches both Pools:

Pool	Holds
Workspace Pool	`workspace.<table>`: members, roles, connected Providers and their connections, Preferences, and the non-secret ingestion and authentication settings of each connected Provider.
Provider Pool	`providers.<connector>.<table>`: the data synced from connected Providers.

It returns only the Pools your role can query. With the Provider Pools Reader role, that is the Provider Pool alone.

SELECT table_ref, column_name, description, data_type
FROM search_available_data('open issues and who they are assigned to', 'providers.jiracloud')
ORDER BY score DESC, qualified_name
LIMIT 20

Arguments

The function takes up to three arguments, by position only: search_available_data(query [, scope [, kind]]). Named arguments (query => ...) are refused.

Argument	Meaning
`query`	Plain text describing the data you want, such as `'connected providers and their status'`.
`scope`	Optional pattern over names whose first segment picks the Pool: `providers` or `workspace`. `NULL` or `''` searches both Pools.
`kind`	Optional: `'table'` or `'column'`. `NULL` or `''` returns both.

The scope keeps a search to one Pool, one Provider, or one table, and applies before ranking. It matches one dotted segment at a time:

Scope	Matches
`providers` or `providers.*`	The whole Provider Pool.
`workspace` or `workspace.*`	The whole Workspace Pool.
`providers.jiracloud` or `providers.jiracloud.*`	Every table and column from one Provider.
`providers.*.issues`	Tables named `issues` in every Provider, without their columns.
`providers.jiracloud.issues.*`	The columns of one table, and the fields inside its struct columns.
`providers.jira.`	Every Provider whose name starts with `jira`.
`providers.jiracloud.issues.fields.*`	The fields inside the `fields` struct column.
`workspace.providers.*`	The columns of `workspace.providers`.

A scope that stops above the table level means everything under it; one that reaches the table level without a trailing * means the tables alone. A scope must start with providers or workspace. Underscores are literal. A double-quoted segment is one literal name, dots and * included, so a column_name from a result works as a scope. A scope and kind that can never match together, such as providers.jiracloud.issues with 'column', are refused with the scope to use instead.

Results

Each row is one table, column, or field, best first.

Column	Meaning
`pool`	`providers` or `workspace`.
`kind`	`table` or `column`. A field inside a struct column is a `column`.
`table_ref`	The name to put in `FROM`, such as `providers.jiracloud.issues`.
`column_name`	The column to select, such as `priority` or `fields.priority.name`, written the way SQL needs it: a name with capitals or spaces keeps its double quotes, such as `"Cost per seat"`. `NULL` for a table.
`qualified_name`	The full dotted name.
`provider`, `provider_website`	The Provider the data comes from. `NULL` for Workspace tables that belong to no Provider.
`description`	What the table or column holds, from the Provider’s schema, the Provider’s data stream, or the Workspace Pool’s definition.
`data_type`	The Arrow type, with its Parable scalar when it has one. A struct lists its field names, quoted where SQL needs them, rather than their types.
`score`	Relevance; higher is better, comparable within one search only.
`matched_by`	How the result matched: `keyword` (the query’s words), `semantic` (its meaning), or `both`. See How results are ranked.

One search returns at most its 500 best results. Page through them with ORDER BY score DESC, qualified_name and LIMIT/OFFSET; qualified_name orders results with equal scores, so pages neither repeat nor skip one:

SELECT table_ref, column_name, data_type
FROM search_available_data('issue priority', 'providers.jiracloud', 'column')
ORDER BY score DESC, qualified_name
LIMIT 20 OFFSET 20

WHERE filters only those 500 results, after ranking. Restrict by Pool, Provider, or table with scope, and use WHERE to narrow what came back, such as WHERE column_name LIKE '%email%'.

How results are ranked

A search ranks by keyword and, where semantic ranking is configured, by meaning, then fuses the two rankings into one. Keyword ranking (BM25) runs over the Workspace’s live catalog. Each table, column, and field is matched on:

its names: the Provider’s connector name, the table, the column, and the field path, split on _, ., and changes of case, so assignee_account_id matches “assignee account”;
its description and title;
its Provider’s name;
for a table, its data stream’s purpose, at half weight.

An entry’s own name counts double; the table and column names above it count once, so github issue labels finds the issues__labels table before its bookkeeping columns. Entries that match more of the query’s words rank above entries that match fewer. Plural and singular forms match (issue finds issues), and common words such as “which” and “the” are ignored. A query that is exactly a column’s, field’s, or table’s own name, in any case, ranks that entry first: is_bot finds is_bot even though “is” is ignored as a word. Keyword ranking matches words, not meaning: “who is responsible” does not find assignee unless a description says so. Semantic ranking compares the query’s meaning with a description of each table and column: its names, type, description, table, and Provider, and for a table, its columns. It finds a field whose words the query never uses, such as “how much is the deal worth” for an opportunity’s amount. The two rankings are fused by weighted reciprocal rank. Each ranking’s top places count the most, and by default a place in the semantic ranking counts three times a place in the keyword ranking. An entry both rankings place well ranks first, and an entry either one finds is still returned. matched_by says which ranking found each result, and score is the fused score. Semantic ranking is available where it is configured: the Workspace has a semantic index of its catalog, and the query layer can embed the query. It is not yet enabled in hosted Workspaces. Wherever it is not available, and for any search where it does not answer in time, results rank by keyword alone: every result then has matched_by = 'keyword' and score is the keyword score, so describe the data in the words a name or description would use. Semantic ranking follows the Workspace’s AI policy. Google Vertex AI embeds the query, in the Workspace’s own Vertex AI project when it has one. When the policy does not allow Vertex AI for the Workspace, every search ranks by keyword alone and the query is sent to no AI processor. The live catalog decides what exists: a dropped or renamed column is never returned, even while the semantic index still holds it, and a column added a moment ago is found by keyword on the next catalog build, before the semantic index has it. Each page read with LIMIT/OFFSET runs the search again. Pages of one query come from the same ranking, except when semantic ranking answers one page and not the next; matched_by then shows the change.

Fields inside struct columns

Some Provider tables keep an object in one struct column, such as the fields column of providers.jiracloud.issues, which holds an issue’s priority, assignee, and status. Each field inside a struct column is its own result, down to three levels below the column, at most 500 per table, shallowest first. Its column_name is the path to select, quoted where SQL needs it:

SELECT key, fields.priority.name, fields.assignee."displayName"
FROM providers.jiracloud.issues
LIMIT 20

Unquoted names fold to lowercase, so a field with capital letters keeps its double quotes. A field has its name and type; its description is the one on the column that holds it.

Where the function runs

The function runs in interactive SQL against the query layer: Workspace API Flight SQL, Preview a Plot, and Ponder. It needs the same data access as any other query in the Workspace and reads only that Workspace’s catalog. A Plot cannot call it. Saving a Plot whose SQL calls search_available_data records the error PV_STMT_003 on the Plot, and the Plot cannot publish until the call is removed; planning the Plot fails with QL-PL-004. Use a search to find names, then write the Plot’s SQL with those names. A listed table can still be empty; check with a bounded query.

Admin

​Arguments

​Results

​How results are ranked

​Fields inside struct columns

​Where the function runs

Arguments

Results

How results are ranked

Fields inside struct columns

Where the function runs