ElasticSearch map two sql tables with a foreign key

In the SQL world, everything is about keeping relationships between tables in such a way that data is never repeated twice (or as seldom as possible), hence the primary-key/foreign-key approach.

In the NoSQL world in general, and in Elasticsearch in particular, there are no explicit relationships between your indices and types. There are ways to create relationships between documents themselves (e.g. parent-child or nested objects), but there is no concept of JOIN.

Instead, the main idea is to denormalize your data in such a way that your documents will be "usable" to carry out your specific use cases. One should not be afraid of keeping redundant copies of data. More generally, you need to ask yourself the following two questions (among many others):

1. what data/fields do you need to display to your users?
2. what queries do you need to perform to retrieve the above data?

In the simple case you highlighted, I would definitely go with a document that contains the JOIN of your two tables:

{    "ID": 1,    "Name": "Episode 2",    "TVSeriesID": 4,    "TVSeriesName": "Friends"}

It is no big deal that the title Friends will be contained in 236 documents, the idea is that you can retrieve any episode and it contains all the data you need to know about it.