Elasticsearch query relational data

Question

I've two different indices on a Elasticsearch server. On index A document have the fields "date" and "fieldX" and on on index B I have the fields "date" and "fieldY".

The date element is a 1:1 relation, meaning for every date there's one document in index A and B. I want to count the documents with the value X of "fieldX" and the value Y of "fieldY" with the matching dates.

I know elasticsearch is not intended for such queries, but I can't change the data structure in this case.

jaspreet chahal · Accepted Answer · 2020-05-26 08:37:38Z

1

Since there are no joins in elastic search a single query cannot check records from both index. If you can change mapping you can look for nested type and parent-child mapping.

As mentioned in your question, you cannot change mapping of the indices. Then you need to make either two separate call for each index or use _msearch to send multiple requests at once.

GET _msearch
{"index":"index85"}
{"query":{"term":{"date":"2020-05-26"}},"aggs":{"count":{"value_count":{"field":"date"}}}}
{"index":"index86"}
{"query":{"term":{"date":"2020-05-26"}},"aggs":{"count":{"value_count":{"field":"date"}}}}

answered May 26, 2020 at 8:37

jaspreet chahal

9,1392 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

michael Over a year ago

Thanks a lot for the fast response. With _msearch I would have to do this search for every single date 'manually'? I'm planning to create a visualization, so this probably won't be suitable.

jaspreet chahal Over a year ago

@daniel I don't have experience with visualization so don't know if it will support it or not. You can write normal queries in _msearch, use a date histogram aggregation for each date

Collectives™ on Stack Overflow

Elasticsearch query relational data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related