Load Data from other index without searching in this index

Hi,

Let's say that I have this 2 indices:
{ "films": { "mappings": { "properties": { "id": {"type": long}, "title": {"type": keyword}, "release_date": {"type": date}, "songs": {"id": {"type": long}} } } } }

{ "songs": { "mappings": { "properties": { "id": {"type": long}, "name": {"type": keyword}, "type": {"type": keyword}, "films": {"id": {"type": long}} } } } }

Is there a way I could search on the films index and load automatically the corresponding songs in a single request ?
It looks like a basic SQL JOIN query which I know is

prohibitively expensive

but it's not really what I want to do. I don't want to search on both indices, only one at a time, but load the corresponding data from the other index.

In some ORMs, this is call eager loading, but I don't think that eager loading means the same thing in elasticsearch.

if this is stored in two indices, then you have to execute two queries.

You could try to store such data in a single index or even in a single document. Like one film doucment that also contains all the songs (or at least the names), if that works for you. This way you could circumvent all the double querying be remodeling your data.

if this is stored in two indices, then you have to execute two queries.

This kind of answers my question. I was trying to load data from 2 indices in one query, so at least I understand that it is not possible.

The things is, the data in each index are going to be updated pretty frequently and independently from each other. So, I would say that storing in one index could be fine but not in one document i guess.

So let's say I'm storing these 2 mappings in one index, would a Parent-Child relationship be a good way to linked them?

So let's say I'm storing these 2 mappings in one index, would a Parent-Child relationship be a good way to linked them?

Yes, the join datatype (got renamed over the last years) is a good idea then.

So let's say I'm storing these 2 mappings in one index, would a Parent-Child relationship be a good way to linked them?

Actually, it's not what I'm looking for.

An element can have multiple children but only one parent.

https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html#_parent_join_restrictions

In my scenario, I want to be able to look for every songs in a film and every films where a song appears. So I would need something like a SQL Many to Many relation, which the join datatype can't do apparently.

Maybe this could be possible with an aggregation?

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