Know SQL? Get search results from Elasticsearch

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 Select
SQL Delete
SQL Where
SQL Order By
SQL Group By
SQL Limit (default is 200)

Conditions:

SQL Like
SQL AND & OR
SQL COUNT distinct
SQL In
SQL Between
SQL Aliases
SQL(ES) Date
SQL now()
SQL NOT

Basic aggregations:

SQL avg()
SQL count()
SQL last()
SQL max()
SQL min()
SQL sum()
SQL Fields

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.