Create a search bar

A search bar can help users easily filter data and find the content they're looking for.

This tutorial will walk through how to use a Search Bar to find data. For other ways to filter, see Create a filter.

The search bar works by accepting user input, then changing a SQL parameter in a Query to filter the data accordingly. If a table is using this specific query node as its data source, the table contents will reflect the output of the search.

In this tutorial, we'll reference the following table:

company_daily_stock_price

Setting up the Query

The first step to creating a search bar is to set up a Query with a SQL parameter. Add a Query from your Table, then double click to open it. Syntax for creating query and parameters can differ depending on the type of the database. See Query for syntax and parameter details.

The following searching examples are all queries made in PostgreSQL query nodes. To create such a Query, locate the company_daily_stock_price in table node list and create a Query from it.

Create the query node from company_daily_stock_price table node

Basic search

A basic search will find all records that match the search value exactly. For example, let's say we want to search based on the column ticker.

First, we need to insert a parameter to represent the search value. Click the plus button at the parameter panel to add a parameter. In the example below, I add a string parameter named "name", and set the default value to "" (empty string).

Basic search: Case-sensitive

The following query will pull records that match the company name exactly, including capital and lowercase letters. If there is no search value, the entire table will be displayed.

Query:

SELECT * 
FROM company_daily_stock_price
WHERE ticker = '{{name}}'  or '{{name}}' = ''

Basic search: Case-insensitive

The query can also be case-insensitive, pulling all matching records regardless of capital or lowercase letters. This is accomplished by adding LOWER() function at both.

Query:

SELECT * 
FROM company_daily_stock_price
WHERE LOWER(ticker) = LOWER('{{name}}')  or '{{name}}' = ''

Full-text search

A full-text search will match all records that contain the search value. This will require us to turn the parameter value into a regular expression, or Regex, using the CONCAT() function.

The following query will concat a % symbol to either end of the parameter value. This tells the query to match all records that contain the search value, regardless of additional characters preceding or following it.

It is also case-insensitive. For example, the searches App, ple, and apple will all match with Apple Inc.. When there is no search value, all records will be matched.

You may also construct other regex patterns to further customize your search.

Query:

SELECT * 
FROM company_daily_stock_price
WHERE ticker LIKE CONCAT('%', '{{name}}', '%');

Full-text Search(Case-insensitive)

In addition to a regular full-text search, you can perform a case-insensitive search by applying the LOWER() function to both expressions that surround the LIKE operator. This will ensure that the search is not sensitive to uppercase or lowercase characters, allowing for a more flexible search.

Query:

SELECT * 
FROM company_daily_stock_price
WHERE (LOWER(ticker) LIKE CONCAT('%',LOWER('{{name}}'),'%'));

By using the LOWER() function, the company_name and search name values are converted to lowercase before comparison, enabling case-insensitive matching. This means that regardless of whether the characters are uppercase or lowercase, the search will match the corresponding records.

Global search

In addition to individual column searches, you can perform global searches that search across multiple columns. This can be done by adding OR operators in the WHERE clause. The following example performs full-text global searches across two columns: company_name and ticker.

Query:

SELECT * 
FROM company_daily_stock_price
WHERE company_name LIKE CONCAT('%', '{{name}}', '%')
    OR ticker LIKE CONCAT('%', '{{name}}', '%');

Search by filtering the data

Once the node is ready, create a search bar on your page by using the Search Bar element group.

The search bar has a supported Search event that is triggered when a user clicks on the Search button or when the user presses Enter.

Use the search event to create an interaction on the search bar to set the SQL parameter in your Query. In the action parameters, use ${event.value} to access the user's search value.