SQL Query for Database Collection

SQL Query Format for Database Collection*

SQL queries in SearchBlox must follow these rules:

• The SQL query should be written as a single line.
• Only one query is allowed per collection.
• A unique field must be mapped to the uid field in the query.
• For applications like Salesforce or Gmail, SQL keywords such as SELECT, FROM, etc., should be in capital letters.
• Fields can be mapped to SearchBlox user-defined fields. The following lists the fields available for mapping.

Sample Database

The table below shows sample data used for the example queries on this page.

Sample SQL Query

🚧

Important Information

• When mapping a field to the uid in SearchBlox, ensure it is unique.
• The SQL query must be written as a single line.
• Avoid adding new line characters, as this can cause indexing issues.

• 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.

select *,<primary or unique field> as uid from <tablename>
select *,no as uid from sample

• Map one field to title to get correct results in faceted or regular search.
• In this query, all fields are fetched and mapped to uid and title.

select *,<primary or unique field> as uid,<title field> as title from table
select *,no as uid, fname as title from sample

• You can limit the number of fields fetched in a query.
• In this query, fields are mapped to uid, title, and content, with only three fields fetched for indexing.

select <index field> as uid , <column name2> as title, <column name3> as content from table
select no as uid, fname as title,address as content from sample

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

select <index field> as uid,<column2> as title,<column3> as description,<column4> as keywords, <column5> as content,<column 6> from table
select no as uid, fname as title,lname as description, dept as keywords, address as content from sample

• In this query, some fields are not mapped but are still indexed as-is.

select <index field> as uid,<column2>, <column3>, <column4>, <column5> from table
select no as uid, fname,lname, dept, address from sample

• The field mapped as uid will be indexed as uid, the field mapped as title will appear as the title, and similarly, fields mapped as description, keywords, and content will appear in their respective sections in search results.
• Queries can also be provided for views in SearchBlox, and data from the view will be indexed based on the SELECT query provided.

Reserved Words in SearchBlox

• These words are reserved in SearchBlox and cannot be used directly in queries:

  • indexdate
  • lastmodified
  • colname
  • url
  • source
  • col

• You must map or rename any field that uses a reserved word.

• The example query shows how to use a reserved word in a query.

select id as uid,name as title, source as "mysource", indexdate as "mydate",url as "myurl" from table

Exceptions in Query for Oracle

• Oracle databases have specific requirements for queries.
• You must explicitly list all fields needed in the SELECT query.
• The following select all query will not work in Oracle:

select *,<index field> as “uid”,<title field> as "title" from table

• Use double quotes when mapping fields in Oracle.
• The following query works with oracle

select <index field> as “uid”,<varchar field> as "title", <varchar field> as "content" from table
select no as “uid”,fname as "title", address as "content" from table

• Oracle has reserved words like "uid", and column names are stored in capital letters.
• Always specify column names in double quotes as shown in the example query.
• Do not use a semicolon at the end of the query for Oracle.

SQL Query for External Database Connectors

• Each database connector uses a different query format.

• For more details on settings, see: Supported Database Connectors List

• SQL keywords in the SELECT query should be in uppercase:

  • SELECT
  • FROM
  • AS

• To index more than 100 rows, use a LIMIT value in the query.

• If no LIMIT is provided, only the top 100 rows will be indexed.

SELECT ShipName, ShipCity AS My_ShipCity, GETDATE() FROM Orders WHERE ShipCountry = 'USA' LIMIT 1000