SELECT DISTINCT documents by a specisific field

Elastic Stack Elasticsearch
1

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!

2

Hi @JohnWhite2000.

Thanks

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

aggregations_ES

Thanks
HadoopHelp

3

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
        		}
        	}
        }
    }
}