How to Model Complex Relational Data Into a Flattened ElasticSearch Index Structure?

ElasticSearch Index Structure with Multiple Entity Bindings

Understanding the need to optimize database structures for ElasticSearch (ES) integration, it's essential to explore how to model complex relational data into a flattened ES index structure.

Multiple Entity Bindings in a Flat Structure

Denormalization is a key technique for optimizing data storage. For instance, instead of maintaining an N:M relationship between products and flags via a pivot table, consider creating a flat ES index where each product document contains an array of associated flags.

Product Document Structure

The flattened product documents would resemble the following structure:

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

Product Mapping Type for ES Index

The ES mapping type for this flattened structure would look similar to the following:

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"
                }
            }
        }
    }
}

Extracting Data using SQL Query

To fetch the necessary data from the database for Logstash processing, a SQL query can be utilized as follows:

The above is the detailed content of How to Model Complex Relational Data Into a Flattened ElasticSearch Index Structure?. For more information, please follow other related articles on the PHP Chinese website!