SELECT DISTINCT documents by a specisific field

I have documents with these fields

  • id
  • userId
  • userFirstName
  • userLastName
  • userEmail
  • projectId

which is something like a many to many, where multiple documents with the same userId can exist.
I need to retrieve all the documents which are unique by userId, I am interested in all user fields, I can ignore project.
If it was relational I would done something like SELECT DISTINCT(userId), userFirstName, userLastName FROM users; but in ES I am not sure. Looking into aggregations but can not make it work yet.

A little help please :slight_smile:
Thank you!

Hi @JohnWhite2000.


I think DISTINCT NOT implemented Yet ES.

You have to use aggrigation if possible in your case but this will fetch single records and that is like :- below



This seems to give me what I want, not sure if there is a better way, but for now it will do

"aggs": {
    "groupedByUserId": {
        "terms": {
            "field": "userId"
        "aggs": {
        	"oneRecord": {
        		"top_hits": {
        			"size": 1

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