How to retrieve filtered values from an external database with multiple properties

Let’s say you have a database of products that are distinguishable on multiple properties, like the type of product, the brand and the color:

When the user says “I’m looking for a black Samsung TV”, the chatbot should respond with something like “TVSamsungBlack is just the right product for you!” and send the URL for the product page.

In order to set this up, we need to do 4 things:

1. Capture the user input
2. Retrieve the database
3. Extract the right product and product URL from the database
4. Return the values to the user

1. Capture the user input

For capturing the user input we used entities for the properties, and combined them all in a single intent.

We set all the entities to required, so we can easily ask the user for potential missing properties:

Learn more about How to capture user input.

2. Retrieve the database

In order to be able to easily retrieve our database, we’ll import it to restdb.io.

To do that, we first need to convert the Google Spreadsheet to a CSV file.

You don’t need to format it anymore. This CSV file can directly be uploaded to restdb.io and you are good to go.

As you can see, we now have the database on restdb.io:

Next, click on the gear icon on the top right corner to enter Developer mode, and head over to the settings, and then the JavaScript API Docs. There you can find your URL and API Key.

3. Extract the right product and product URL from the database

Copy and paste your URL and API Key from restdb.io into this code, and also change the entity names to yours:

async payload => {

   var request = require("request");
  var options = { method: 'GET',
  url: 'https://test1-9795.restdb.io/rest/database-example-sheet-1',
  headers: 
   { 'cache-control': 'no-cache',
     'x-apikey': '6abea6430b6ef2e86c7197f7df47c0076721c' } };

  let p = new Promise((res,rej) => request(options, function (error, response, body) {
    if (error) rej(error)
    
    res(body)
  }))

  let body = await p
  body = JSON.parse(body)
  
  console.log('b',body)
  
  const product_type = payload.params.product_type[0].value,
  brand = payload.params.brand[0].value,
  color = payload.params.color[0].value
  console.log(product_type, brand, color)

  const data = body.filter((e) => e.product_type === product_type && e.brand === brand && e.color === color);
  
  console.log(data)
  return {
    params: {
      // Using a spread operator, we add all provided params in the result
      ...payload.params,
      // Now we override the product param
      product: [{
        value: data[0].product,
        match: 'product'
      }],
      product_url: [{
        value: data[0].product_url,
        match: 'product_url'
      }]
    }
  }

}

You can also add more filters if you have 4 or 5 properties instead of the 3 in this example of course.

4. Return the values to the user

After the code action has been triggered, the bot will now have values for the parameters “product” and “product_url’, so we can use them in our bot responses like any other parameter in Flow.ai:

When we test this flow, you can see it’s all working now!