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!


Using SQL in Elasticsearch

Viewing Results from Elasticsearch

Using SQL, you can view search results directly from Elasticsearch using the following URL (available with versions 8.6 and later):
https://localhost:9200/_opendistro/_sql?sql=select * from idx001

You can also export the results in CSV format using the URL below:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select url, title from idx001

Basic Queries

Use the following queries for specific search results you need.

Queries for specific file types:
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where contenttype='pdf'

Queries for specific URL or filename:
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where url=''

Queries for specific keywords:
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where keywords='cnn'
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where keywords IN ('cnn','world'))

Results from multiple indices:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001,idx003,idx003

Results with only URLs from indices:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select uid from idx001,idx002

Query with ORDER BY:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select uid, title from idx001,idx002 order by size

Query with LIMIT:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select uid from idx001,idx002 limit 10

Use exclude ('fieldname') and include ('fieldname') to exclude fields or include fields in display. Partial name of fields can be given along with * character.

https://localhost:9200/_opendistro/_sql?format=csv&sql=select exclude('a*') from idx001,idx003

https://localhost:9200/_opendistro/_sql?format=csv&sql=select exclude('c*') from idx001,idx003

All fields within Elasticsearch can be accessed using SQL.


Group by and count together can be used to determine the count of file types in results.
[https://localhost:9200/_opendistro/_sql?format=csv&sql=select count(*) from idx001 group by contenttype

Group by can be also used to determine sentiment in sentiment analysis.
[https://localhost:9200/_opendistro/_sql?format=csv&sql=select count(*) from idx001 group by content.sentiment

Query with sum function:
https://localhost:9200/_opendistro/_sql?format=csv&sql=SELECT sum(size) from idx001

Queries with min and max functions:
https://localhost:9200/_opendistro/_sql?sql=select min(size) from idx001
https://localhost:9200/_opendistro/_sql?sql=select max(size) from idx001

Queries with statistic functions:
https://localhost:9200/_opendistro/_sql?sql=select avg(size) from idx001
https://localhost:9200/_opendistro/_sql?sql=select extended_stats(size) from idx001
https://localhost:9200/_opendistro/_sql?sql=select percentiles(size) from idx001

Beyond SQL features

https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author is missing
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author is not missing

Query to search for multiple terms using IN_TERMS:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author= IN_TERMS(hattie,alis,george)

Query to search for a specific term using TERM:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author= TERM(george)

Other special queries related to statistics that can be used in Elasticsearch:
https://localhost:9200/_opendistro/_sql?format=csv&sql=SELECT extended_stats(age) FROM idx001

https://localhost:9200/_opendistro/_sql?format=csv&sql=SELECT percentiles(age) FROM idx001

License Key Required

A SearchBlox license key is required for accessing this feature.

Updated 9 months ago

Using SQL in Elasticsearch

Suggested Edits are limited on API Reference Pages

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