Using SQL in Opensearch
Use SQL in Opensearch to download data from the search index. All fields within Opensearch can be accessed using SQL.
You can perform the operations on POSTMAN, KIBANA and curl commands using the method POST
Important Information
You can find the Opensearch login credentials in the searchblox.yml file, found in the following file path:
\SearchBloxServer\webapps\ROOT\WEB-INF
Viewing Results from Opensearch
Using SQL, you can view search results directly from Opensearch using the following URL.
Using POSTMAN:
METHOD : POST
Endpoint : https://localhost:9200/_plugins/_sql
Payload : JSON
{
"query" : "SELECT * FROM idx001"
}
You can also export the results in CSV format using the following URL.
METHOD : POST
Endpoint : https://localhost:9200/_plugins/_sql?format=csv
Payload : JSON
{
"query" : "select url, title from idx001"
}
Basic Queries
Use the following queries for specific search results.
Endpoint : https://localhost:9200/_plugins/_sql
| Topic | Query |
|---|---|
| Queries for specific file types | select * from idx001 where contenttype='pdf' |
| Queries for specific URL or filename | select * from idx001 where url='http://www.bbc.com' |
| Queries for specific keywords | select * from idx001 where keywords='cnn' |
| Queries for specific keywords | select * from idx001 where keywords IN ('cnn','world') |
| Queries with min function | select min(size) from idx001 |
| Queries with max function | select max(size) from idx001 |
| Queries with statistic functions | select avg(size) from idx001 select extended_stats(size) from idx001 select percentiles(size) from idx001 |
Endpoint : https://localhost:9200/_plugins/_sql?format=csv
| Topic | Query |
|---|---|
| Results from multiple indices | select * from idx001,idx003,idx003 |
| Results with only URLs from indices | select url from idx001,idx002 |
| Query with ORDER BY | select uid, title from idx001,idx002 order by size |
| Query with LIMIT | select uid from idx001,idx002 limit 10 |
| Query with sum function | SELECT sum(size) from idx001 |
Query with INCLUDE, EXCLUDE
Use exclude ('fieldname') and include ('fieldname') to exclude fields or include fields in display. Partial name of fields can be given along with * character.
{
"query" : "select exclude('a*' from idx001,idx003"
}
{
"query" : "select exclude('c*') from idx001,idx003"
}
Aggregations
Using GROUP BY and COUNT
Group by and count together can be used to determine the count of file types in results.
{
"query" : "select count(*) from idx001 group by contenttype"
}
Group by can be also used to determine sentiment in sentiment analysis.
{
"query" : "select count(*) from idx001 group by content.sentiment"
}
Beyond SQL features
Endpoint : https://localhost:9200/_plugins/_sql?format=csv
| Topic | Query |
|---|---|
| Query with IS MISSING | select * from idx001 where author is missing |
| Query with IS NOT MISSING | select * from idx001 where author is not missing |
| Query to search for multiple terms using IN_TERMS | select * from idx001 where author= IN_TERMS(hattie,alis,george) |
| Query to search for a specific term using TERM | select * from idx001 where author= TERM(george) |
| Special queries related to statistics that can be used in Opensearch | SELECT extended_stats(age) FROM idx001 SELECT percentiles(age) FROM idx001 |
Updated 9 months ago