How do I write an ElasticSearch query to find unique elements in columns?

Elastic Stack Elasticsearch
1

For example, if I have a SQL query:

SELECT distinct emp_id, salary FROM TABLE_EMPLOYEE
what would be its ElasticSearch equivalent?

This is what I have come up with until now:

{
"aggs": {
"Employee": {
"terms": {
"field":["emp_id", "salary" ]
"size": 1000
}
}
}
}

2

You'll need a separate terms aggregation for each field here. Try the following instead:

{
	"aggs": {
		"Employee": {
			"terms": {
				"field": "emp_id"
				"size": 1000
			},
			"aggs": {
				Salary: {
					"terms": {
						"field":"salary"
						"size": 100
					}
				}
			}
		}
	}
}

Note that this is still slightly different from the SQL query you posted because it will give you the top 1000 employees ordered by doc_count and for each employee the top 100 salaries ordered by doc_count.

Hope that helps

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.