DownloadsDocumentationAPI ReferenceChangelogOpen A TicketContact

## **SQL Query Format for Database Collection**

SQL Queries in SearchBlox have a few basic rules:

  • SQL Query must be given as a single line.

  • Only one query can be given per collection.

  • It is mandatory to map a unique field to **uid** field in the query.

  • Applications supported by database collections (such as Salesforce or Gmail) should have the query words such as "SELECT", "FROM", etc., in capital letters.

  • Fields can be mapped to the SearchBlox fields. The following lists the user-defined fields within SearchBlox to which field values can be mapped.

FieldDescription
uidA primary or unique field from a table has to be mapped to this field. **This is a mandatory field. Make sure to map a unique field to this field**
titleIf any field has to appear as a title in search results, it should be mapped as the title.
descriptionIf any field has to appear in the description then it should be mapped to the description field.
keywordsIf any field has to appear in the keywords section it has to be mapped as keywords.
contentIf you want any field to be updated in the content section you can map the same in the query.

## **Sample Database**

The sample data provided in the following table is used for the sample queries on this page.

nofnamelnameaddressdeptdept iduniversity
1SmithJones1, GeorgetownBiotechD05VCU
2JamesBrown5, NearvillePhysicsD01VCU
3WillSmith3, NapatownBioinfoD04VCU

### Sample SQL Query

Important Information

  • If you need to map a field to the **uid** field in SearchBlox, always make sure that it is unique.

  • Note that the SQL query must be given as a single line.

  • Do not enter any new line characters in between, as this could cause problems with indexing.

  • The following query is the most simple query that can be given in Database Collection. It is required to map a unique field to uid field.


  • Please map one of the fields to the title to get proper results in faceted or regular search
  • In this query all fields have been fetched and fields have been mapped to uid and title.

  • You can limit the number of fields fetched in a query
  • In these query fields have been mapped to uid, title, and content. Also, only three fields have been fetched for indexing. 

  • In this query all user-defined fields in SearchBlox have been mapped.

  • In this query few fields are not mapped but indexed as it is.

  • Please note that the field mapped as uid will be indexed as uid, field indexed as title will be indexed as title and will appear in the title in the results. Similarly, fields mapped as description, keywords, and content fields will appear as the respective fields in search results.
  • You can also provide queries for views in SearchBlox. The data from the view will be indexed in the collection based on the select query provided for the view.
## **Reserved Words in SearchBlox**
  • The following are reserved words in SearchBlox, and cannot be used in the query directly.
    • indexdate
    • lastmodified
    • colname
    • url
    • source
    • col
  • It is required to map and rename the field with the reserved word. 
  • The following query shows how to use a reserved word in a query

## **Exceptions in Query for Oracle**
  • There are certain standards with respect to the Oracle database.
  • It is required to explicitly specify all the fields required in the select query
  • The following select all query will _not_ work in Oracle: 

  • Double quotes must be provided for mapping fields while using Oracle.
  • The following query works with oracle

  • Oracle has some reserved words such as "uid". Also, the column names are stored in capital letters. 
  • Therefore, it is mandatory to specify the assigned name of columns in double-quotes as in the above query. 
  • Most importantly, do not use a semicolon at the end of the query for Oracle.
## **SQL Query for External Database Connectors**
  • Each database connector has different query format
  • To know more about settings for Database connector read: [Supported Database Connectors List](🔗) 
  • The select query should have the select query terms in uppercase
    • SELECT
    • FROM
    • AS
  • If you have more than 100 entries in a table, specify the number of documents to be indexed by giving LIMIT value in the query.  
  • If no limit value is provided then only top 100 rows would be indexed