SearchBlox

SearchBlox Developer Hub

Welcome to the SearchBlox developer hub. Here you will find comprehensive guides and documentation to help you start working with SearchBlox as quickly as possible, as well as support if you get stuck. Let's jump right in!

Guides

SQL Query for Database Collection

SQL Query Format for DataBase Collection

SQL Queries in SearchBlox have some requirements for the search results to be displayed correctly.

  • SQL Query must be given as a single line.
  • Only one query can be given per collection.
  • The fields can be mapped to the SearchBlox fields (but this is not mandatory). These mapped SearchBlox fields will help with customizing search templates and searching in SearchBlox. The following lists the fields within SearchBlox to which field values can be mapped.
  • Applications supported by database collections (such as Salesforce or Gmail) should have the query words such as "SELECT", "FROM", etc., in capital letters.
Field
Description

UID

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

Title

If any field has to appear as a title in search results, it should be mapped as the title.

Description

If any field has to appear in the description section, it should be mapped as the description.

A few example queries are listed in the section below.

no
fname
lname
address
dept
dept id
university

1

Smith

Jones

1, Georgetown

Biotech

D05

VCU

2

James

Brown

5, Nearville

Physics

D01

VCU

3

Will

Smith

3, Napatown

Bioinfo

D04

VCU

Simple Query
The query below is the most simple query that can be given in Database Collection.
select *,<primary or unique field> as uid from <tablename>

Other Mapped Queries
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.

Example Query 1
Select *,<primary or unique field> as uid,<title field> as title from table

This query will help index all the fields in the table. After searching, the UID will appear as the title.

Example Query 2
Select <index field> as uid , <column name2> as title, <column name3> as content from table

Example Query 3
Select <index field> as uid,<column2> as title,<column3> as description,<column4> as keywords, <column5> as content,<column 6> from table

Example Query 4
Select <index field> as uid,<column2>, <column3>, <column4>, <column5> from table

The above queries will help index the mapped fields from the table. After searching, the field mapped as the title will appear as the title in the search results.

You can also provide queries for views in SearchBlox, which would index the data from views.

Reserved Words in SearchBlox

The following are reserved words in SearchBlox, and cannot be used in the query directly.

  • Indexdate
  • Lastmodified
  • Colname
  • URL
  • Size
  • Col

Example query for using reserved words
Select id as “did”, indexdate as “mydate”,url as “myurl” from table

Exceptions in Query for Oracle

While indexing queries in the Oracle database, it is important to be aware of the rules and standards required. For example, the following query will not work in Oracle:

Select *,<index field> as “uid” from table

Queries similar to the one below only work while indexing Oracle databases in SearchBlox. Also, double quotes must be provided for mapping fields while using Oracle.

Example Query 1:
Select <index field> as “uid”,<varchar field> as "title", <varchar field> as content from table

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.

SQL Query for Database Collection


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.