sqltoes (sql to ES)

Writing elasticsearch commands is hard, writing SQL queries is...less hard

Motivation: Elasticsearch commands are confusing to write at a certain point. See the Elasticsearch reference for more help.

Installation

npm install sqltoes

Use

var sqltoes = require('sqltoes')
var query = {select: ['sum(value)'], where: ['commodity = rice'], groupBy: ['description','year']}

sqltoes(query)
// { aggs: { where_commodity_rice: { filter: [Object], aggs: [Object] } } }

JSON.stringify(sqltoes(query))
// '{"aggs":{"where_commodity_rice":{"filter":{"term":{"commodity":"rice"}},"aggs":{"group_by_description":{"terms":{"field":"description","size":1000},"aggs":{"group_by_year":{"terms":{"field":"year","size":1000},"aggs":{"sum_value":{"sum":{"field":"value"}}}}}}}}}}'

How it works (and what works so far)

Looks for three properties: select, where, and groupBy (currently requires them all)

• Anything in the select clause is ignored unless it is wrapped in sum, min, max, or avg all of which become a final aggregation
• The where clause supports two types of queries:
  • [field] = [value] will be treated as a term filter.
  • [field] in ([value1],value[2]) will be treated as a terms filter.
• Anything in the groupBy clause is treated like the second example shown here.

Similar works

• sql-where-parser
• sql2es
• sql-es