Join two indices query where there is 1-to-many relationship

I have two indices; authors and articles.
Since one author writes several articles, there are 1-to-many relationship between them.
The article index has title, vote, and authorId attributes.

Here is an example index.

PUT /test_authors/
  "mappings": {
    "properties": {
       "name": {"type": "keyword"},

PUT /test_articles/
    "properties": {
        "title": {"type": "keyword"},
        "vote": {"type": "long"},
        "authorId": {"type": "long"},

sample documents

// sample authors
POST /test_authors/_bulk
{"name": "Mick"}
{"name": "Tim"}

// sample articles
POST /test_articles/_bulk
{"title": "About car", "vote": 5, "authorId": 1}
{"title": "About sport", "vote": 2, "authorId": 1}
{"title": "About computer", "vote": 1, "authorId": 2}

What I want to do is to search authors, one of whose articles has more than 3 votes.
In the above example, expected result should be only Mick(authorId=1).

In the world of rds, I can do it easily through by single query.

LEFT JOIN articles
ON articles.authorId =

Can I realize like this kind of thing in elasticsearch?
If not, how can I do this?
Thank you in advance.

Elasticsearch does not support joins so the most common workaround is to denormalise your data and store the relevant authour data on each arcticle document.

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