SQL Query for Database Collection

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.

Sample Database

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

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.

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

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

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 these query fields have been mapped to uid, title, and content. Also, only three fields have been 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 few fields are not mapped but indexed as it is.

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

• 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

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

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:

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

• Double quotes must be provided for mapping fields while using 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 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

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