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.

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.
`matched_by`	How the result matched. Always `keyword`.

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

The search is keyword ranking (BM25) 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. The search matches words, not meaning: “who is responsible” does not find assignee unless a description says so, so describe the data in the words a name or description would use. The live catalog decides what exists: a dropped or renamed column is never returned, and a column added a moment ago is found on the next catalog build.

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.

​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