Skip to main content

​
Pool search

Pool search finds the tables, columns, and fields that hold the data a question needs, before any SQL is written. A Workspace’s Pools can hold hundreds of tables and thousands of columns, and names such as status, name, and type repeat across them. Pool search exists so Ponder uses the exact names instead of guessing them or reading the whole catalog. It searches the available data, meaning the names, descriptions, and types of tables and columns. It never searches rows. Questions about what records say, such as which calls mentioned a pricing concern, are Row search (search_everything), not Pool search. One SQL table function does the search: search_available_data. It covers both Pools: the Workspace Pool, workspace.<table>, which holds members, roles, connected Providers and their connections, Preferences, and the non-secret settings of each connected Provider; and the Provider Pool, providers.<connector>.<table>, which holds the data synced from connected Providers. It takes up to three arguments, by position only: query, then scope, then kind. The query is plain text describing the data you want. The optional scope is a dotted pattern whose first part picks the Pool: providers.* or workspace.. providers.jiracloud (or providers.jiracloud.) is everything from one Provider, providers..issues is one table name across Providers, providers.jiracloud.issues. is the columns of one table, and workspace.providers.* is the columns of one Workspace table. With no scope, both Pools are searched. The optional kind is ‘table’ or ‘column’. Pass NULL to skip scope and still set kind. Run a search like any other SQL, with Preview a Plot, the operation that runs one query and returns its rows without saving a Report.
Each result row is one table or one column. table_ref is the name to put in FROM, such as providers.jiracloud.issues or workspace.providers. column_name is the column to select, written the way SQL needs it, so a name with capitals or spaces keeps its double quotes, such as “Cost per seat”. qualified_name is the full dotted name. pool and kind say where the result lives and what it is. provider and provider_website name the Provider behind a Provider Pool table. description says what the table or column holds, and data_type gives its type, with the Parable type in parentheses when it has one. Results come best first by score. Search ranks by the words of the query against names, descriptions, and Provider names, and, where semantic ranking is configured, by the meaning of the query against a description of each table and column. matched_by says which found a result: keyword, semantic, or both. Where semantic ranking is not available, every result says keyword. Some Providers keep related values together in one struct column, such as the fields column of Jira Cloud issues. Each value inside it is its own result, up to three levels deep, and its column_name is the whole path to select. A struct’s data_type lists the names inside it, each written the way SQL needs it. Keep the double quotes around a name with capital letters: SQL reads an unquoted name in lowercase, so displayName without its quotes is not found.
Searches run only in interactive queries: through Preview a Plot or Query the Provider Pool, or with Search available data, which runs one for you and returns the same columns. Query the Provider Pool and Search available data reach the Provider Pool only: through them a workspace scope finds nothing and a workspace.<table> is an unknown relation, so see which Providers are connected through the Provider connections instead of workspace.providers, or list the Provider Pool: Search available data with an empty query returns each Provider with its table count, and with a Provider’s scope, such as providers.jiracloud, that Provider’s tables a page at a time. A Plot cannot call search_available_data: saving one records an error on the Plot, and the Plot cannot publish until it is removed. Use a search to find names, then write the Plot’s SQL with those names. A search reads only the current Workspace, needs the same data access as any other query, and never widens it. A listed table can still be empty.
  1. Describe the data in plain words, such as issue priority or connected providers, rather than a guessed column name. Prefer the words a table or column would use: they match by keyword everywhere, while a paraphrase such as who is responsible for the ticket finds the assignee only where semantic ranking is on. Through Preview a Plot, search both Pools unless you already know which one holds the answer: people, roles, and Providers are in the Workspace Pool, and work data is in the Provider Pool. Through Query the Provider Pool or Search available data, only the Provider Pool is searched.
  2. Find the table first. Pass kind ‘table’ to list only tables, as in search_available_data(‘sprints and their dates’, NULL, ‘table’).
  3. Then find its columns. Scope a second search to the chosen table, such as providers.jiracloud.issues., or to the values inside one column, such as providers.jiracloud.issues.fields., and read description and data_type to tell apart generic names like status or name.
  4. Keep a LIMIT on every search. Page with ORDER BY score DESC, qualified_name and OFFSET, for example LIMIT 20 OFFSET 20 for the second page; qualified_name breaks ties, so pages do not repeat or skip results. A search returns at most its 500 best results, and WHERE filters only those 500, so narrow with scope rather than WHERE.
  5. Write the real query with the exact table_ref and column_name the search returned, and run it the same way. Never retype, shorten, pluralize, or unquote a name.
  6. If nothing fits, rephrase the query with other words a name or description would use, or widen the scope. If a Provider’s data is missing entirely, the Provider may not be connected, or its stream may not be enabled. Through Preview a Plot, read workspace.providers and workspace.provider_connections. Through Query the Provider Pool or Search available data, list the Provider Pool with an empty search instead: a Provider it does not name has no tables you can read.
A good result names the table_ref and columns it used and why they fit, based on their descriptions, and its real query returns rows with the expected grain. Check the choice with a bounded query that counts the table’s rows and reads a few values. A search result alone does not prove that rows exist or are current. Related Powers: Row search, Providers, Workspaces, Plots, Preferences. Find each by its title; do not guess stored names.