Tables

Tables are an essential feature in the explorer, functioning as a local database within your chatbot's environment. They provide structured storage for various types of data that can be accessed and manipulated as required within your workflows.

They are particularly useful for storing information that needs to be persisted across multiple sessions, such as user profiles, transaction histories, and other types of data that need to be retrieved and updated over time.

Creating a Table

To add a new table, go to the explorer menu and select Add Table. You can then give your table a name and add fields to it. Each field has a name and a type. The type determines the kind of data that can be stored in the field. For example, a field with the type String can store any text value, while a field with the type Number can only store numeric values.

Making Fields Searchable

You can make a field searchable by checking the Searchable checkbox. This will allow you to search for records based on the values in the field. For example, if you have a field called Name and you make it searchable, you can search for records based on the name of the person. This is only available for fields with the type String.

Types of Fields (Columns)

There are several types of fields you can add to a table. Each field type has a specific purpose and can only store certain types of data. These are the field types you can choose from: String, Number, Boolean, Date, and Object.

Query Selector

A Query Selector is the query you use to select or filter data from your table. For instance, you may need to find a specific record based on certain criteria like a user's ID or a transaction number. The Query Selector allows you to articulate these conditions in plain English, making it both intuitive and powerful. An example of a query could be "Find all records where 'user_id' equals '123'".

To add a Query Selector, click on the table in the explorer menu. You'll then see a list of all the records in the table. Click on the Add Query Selector button from the right panel to add a new query selector. You can then enter the query selector in the text field.

Operations on Tables - via the Interface

Add records to a table

Once you've created a table, you can add records to it. To do this, click on the table in the explorer menu. You'll then see a list of all the records in the table. Click on the Add Record button to add a new record. You can then enter the values for each field in the record.

Update records in a table

Double click on a a table cell or press click it and press Enter to see an input that allows you to change the cell value. Once you are done, press Enter again or click away to save the new value.

Delete records from a table

Right Click on the Record and select Delete Record to delete a record from a table.

Operations on Tables - via Cards

Check out the Table Cards documentation to learn more about operations with cards.

Operations on Tables - via Code

createRecord

To create a new record in a table, use the createRecord function. You can specify the values for each field in the record. This function can be particularly useful when you need to store new information, such as a new user's details or a newly completed transaction.

// Create a record in the table
await Data1Table.createRecord({ Age: 25, Name: 'Tom', Occupation: 'Student' })

createRecords

To create multiple records in a table, use the createRecords function. You can specify the values for each field in the record. Useful for creating multiple records at once.

// Creating two records in the table
await Data1Table.createRecords([
  { Name: 'Tom', Age: 25, Occupation: 'Student', 'Is Friendly': true },
  { Name: 'Didier', Age: 54, Occupation: 'Pilot', 'Is Friendly': true },
])

deleteRecord

To delete a record in a table, use the deleteRecord function. You need to specify the record ID of the record you want to delete.

javascript // Delete a record in the table at record ID 7 await Data1Table.deleteRecord(7)

deleteRecords

To delete multiple records in a table, use the deleteRecords function. You need to specify the record IDs of the records you want to delete in an array.

javascript // Delete records in the table at record IDs 7, 8, 9 await Data1Table.deleteRecords([7, 8, 9])

findRecords

To find records in a table, use the findRecords function. You can add filters, just query everything or search using semantic search (like a Google Search!).

Basic usage and pagination

To retrieve data from a table in Botpress, you can use the findRecords function. This function allows you to specify the limit (number of records to return) and the offset (number of rows to start from).

For instance, if you want the first 10 records, you can set the limit to 10 and the offset to 0. To fetch the next 10 records, you can set the limit to 10 and the offset to 10.

To determine if there are more records available, you can check the length of the array returned. If the length is greater than 0, it indicates the presence of additional records.

Here's an example:

const limit = 10
const startRow = 0
 
workflow.records = await DataTable1.findRecords({
  limit,
  offset: startRow,
})
 
// Checking the second page
const secondPage = await Data1Table.findRecords({
  limit,
  filter: AI`everything`,
  offset: startRow + limit,
})
 
workflow.hasNextPage = secondPage.length > 0 // This flag can be consumed in your bot flow to handle pagination

You can apply a similar approach to determine if there are previous pages as well.

Searching fields with natural language

To perform a traditional search, use the search parameter when calling the DataTable1.findRecords() function in Botpress. Here is an example:

workflow.records = await DataTable1.findRecords({ search: 'red velvet chairs' })

You can pass variables like event.preview to the search parameter to search for user input or any other variable. This allows you to preprocess the user's input before performing the search.

Filtering results

You can filter results using filters. Similar to MongoDB, this code provides methods to filter results. The filtering mechanism can be divided into two main categories: Primitive and Logical.

Don't want to deal with code? Skip to the next section for AI generated filters.

Primitive Filter

Primitive filters are easy and simple to use, allowing you to match the exact values you are looking for. Here are some of the primitives you can use:

• $eq: Matches values that are equal to a specified value.

  { "name": { "$eq": "John" } }

• $gt: Matches values that are greater than a specified value.

  { "age": { "$gt": 20 } }

• $gte: Matches values that are greater than or equal to a specified value.

  { "age": { "$gte": 20 } }

• $lt: Matches values that are less than a specified value.

  { "age": { "$lt": 20 } }

• $lte: Matches values that are less than or equal to a specified value.

  { "age": { "$lte": 20 } }

• $ne: Matches all values that are not equal to a specified value.

  { "name": { "$ne": "John" } }

• $in: Matches any of the values specified in an array.

  { "name": { "$in": ["Alice", "Bob", "John"] } }

• $nin: Matches none of the values specified in an array.

  { "name": { "$nin": ["Alice", "Bob", "John"] } }

• $exists: Matches documents that have the specified field.

  { "name": { "$exists": true } }

• $mod: Performs modulo operation on the value of field and matches documents where field % divisor equals the specified remainder.

  { "qty": { "$mod": [4, 0] } }

• $size: Matches any document where an array field contains a specified number of elements.

  { "tags": { "$size": 3 } }

• $regex: Provides regular expression capabilities for pattern matching strings in queries. It uses a placeholder to prevent SQL injections.

  { "name": { "$regex": "[a-z]" } }

• $options: Modifies the $regex operator to enable options. Currently, there are two options, 'i' for case insensitive and 'c' for case sensitive.

  { "name": { "$regex": "[a-z]", "$options": 'i' } } // case insensitive
  
  { "name": { "$regex": "[a-z]", "$options": 'c' } } // case sensitive

Logical Filter

Logical operators allow you to connect more filtering clauses.

• $and: Joins query clauses with a logical AND. Returns all documents that match the conditions of both clauses.

  { "$and": [{ "price": { "$ne": 1.99 } }, { "price": { "$exists": true } }] }

• $or: Joins query clauses with a logical OR. Returns all documents that match the conditions of either clause.

  { "$or": [{ "price": { "$eq": 1.99 } }, { "price": { "$exists": false } }] }

• $not: Inverts the effect of a query expression and returns documents that do not match the query expression.

  { "$not": { "price": { "$eq": 1.99 } } }

Please note that these filters and operators can be used together to create complex queries to exactly fit your needs.

Using AI to generate filters

Thankfully, Botpress comes with artificial intelligence (AI) capabilities when it comes to filtering records. This means you can provide a human-like query, and the AI will intelligently decipher it and create the right filters for your query.

This can significantly simplify the construction of complex queries and reduce the likelihood of human errors in the process.

const leads = await LeadsTable.findRecords({
  filter: AI`works either at ${workflow.company} OR is not in IT`,
  limit: 10,
  offset: 0,
})

The query is written inside the backticks (`) that come after `filter: AI`. The AI expects a query string, and that's why backticks are used.

Re-using filters (selectors)

Selectors are filters that you can re-use. They don't have to be written again and again.

For example, you might use the query selector "People with age greater than 18" to find all users who are above 18.

1. Create a query selector in the table and give it a name. (e.g. adults)
2. Add query as People with age greater than 18
3. use below code to find records and save it in a variable called adults

// Find records in the table
const data = await Data1Table.findRecords({ selectorName: 'adults' })
workflow.adults = data

Difference between search and AI filters

A standard Search in Botpress is employed to identify specific strings like a user's name. It gives precision-based results by matching specific sets of characters.

On the other hand, an AI Filter broadens the scope and is used primarily for filtering results rather than explicitly searching them. It helps you track down users based on certain criteria, such as age above 25.

Although the AI Filter is not primarily designed for exact string hunting, it's flexible enough to allow it. For instance, you could use it to filter results for Canada as a country.

getRecord

You can use the getRecord function to retrieve a specific record from a table based on a unique identifier. For example, you might use the getRecord function to retrieve a user's profile information based on their unique user ID.

 
// 1 is the record ID and User is the variable name
workflow.User = data
 

updateRecord

You can use the updateRecord function to modify a specific record in a table. This will be useful when you need to update a specific field in the record. For example, you might use this function to update a user's profile information or to update the status of a transaction.

// Update at record ID 8's Age to 45
await Data1Table.updateRecord(8, { Age: 45 })

upsertRecords

The upsertRecords function allows you to update or create records in a table in a single operation. It is useful for any situation where you need to update or insert data into a table without having to worry about whether the rows already exist.

Example:

// Update the name and address of the records with IDs 1 and 2.
await CustomersTable.upsertRecords('id', [
  { id: 1, name: 'John Doe', address: '123 Main Street' },
  { id: 2, name: 'Jane Doe', address: '456 Elm Street' },
])

This code will upsert the records into the CustomersTable. If the records with IDs 1 and 2 already exist in the table, the function will update the existing rows with the new name and address values. If the records with IDs 1 and 2 don't already exist in the table, the function will insert new rows into the table with the new name and address values.

Importing and Exporting

Import a Table

You can import a table from a CSV file. Click on the Import Table button above the table and select the CSV file you want to import. Botpress will automatically detect the column names and types from the CSV file and create the table structure for you.

Export a Table

Click on the Export Table button to export the table. This will download the table as a CSV file to your computer.