By Timo Selvaraj
SearchBlox 8.6.1 provides the ability to get search data in CSV or JSON using simple SQL statements for complex queries including Elasticsearch specific functions and JOINs between 2 elasticsearch indexes.
Get all results in JSON from collection # 1 : http://localhost:9200/_sql?sql=select * from idx001
Get all results in CSV from collection #1 : http://localhost:9200/_sql?format=csv&sql=select * from idx001
Get specific fields from collection #1 : http://localhost:9200/_sql?sql=select url, title from idx001
Get search results for specific keyword from collection #1: http://localhost:9200/_sql?sql=select from idx008 where keywords=’cnn’ or http://localhost:9200/_sql?sql=select from idx008 where keywords IN (‘cnn’,’world’) for getting results for a list of terms
Get search results from multiple indexes :
SELECT url from idx001,idx002 WHERE keywords IN (‘cnn’,’world’)
SQL Statements supported by SearchBlox
SQL Order By
SQL Group By
SQL Limit (default is 200)
SQL AND & OR
SQL COUNT distinct
Fields can be listed out by exact field name or used with the include/exclude syntax for use with wildcards.
include(‘d*’) – include all fields starting with “d”
exclude(‘age’) – include all fields except “age”
include(‘*Name’), exclude(‘lastName’) – include all fields that end with “Name” except “lastName”
To use SQL to access your Elasticsearch data please download and install SearchBlox. Setup one or more collections and use sample SQL statements to get started.