How to Structure an Elasticsearch Index for Multiple Entity Relationships?

How to Structure ElasticSearch Index with Multiple Entity Bindings

Introduction

Integrating ElasticSearch (ES) into existing applications often raises the question of how to replicate complex database structures in the ES index. This article tackles the specific challenge of configuring the index structure when dealing with multiple entity bindings.

Database Structure

Consider the following database structure from an e-commerce application:

• Products (id, title, price)
• Flags (id, title)
• FlagsProducts (pivot table with productId, flagId, externalId)

Flattening the Structure

To optimize for querying and ease of use, it's recommended to denormalize the data by flattening the structure. This involves creating product documents that incorporate all the relevant information from the other tables:

{
"id": "00c8234d71c4e94f725cd432ebc04",
"title": "Alpha",
"price": 589.0,
"flags": ["Sellout", "Top Product"]
}

ES Product Mapping

The corresponding ES product mapping type would be:

PUT products
{
"mappings": {
"product": {
"properties": {
"id": {
"type": "string",
"index": "not_analyzed"
},
"title": {
"type": "string"
},
"price": {
"type": "double",
"null_value": 0.0
},
"flags": {
"type": "string",
"index": "not_analyzed"
}
}
}
}
}

Logstash SQL Query

To populate the ES index, a Logstash JDBC input can be used with the following query:

The above is the detailed content of How to Structure an Elasticsearch Index for Multiple Entity Relationships?. For more information, please follow other related articles on the PHP Chinese website!