I have two mysql tables that I want to save into a single Elastic index.
Table 1: Product has id, name ... etc.
Table 2: ProductWarehouse has productId, warehouseId, qtyAvailable ... etc
Product one-to-many ProductWarehouse
My question is, how can I save ProductWarehouse records for each product as an array?
If ProductWarehouse was modified, how to reflect that on the Elastic index?
What is the best practice?
the Product index in Elastic should look like:
{
"id",
"name": ...,
"warehouses": [
{"warehouseId": 1, "qtyAvailable": 3},
{"warehouseId": 10, "qtyAvailable": 30}
]
}
My suggestion is to create a MySQL view of the structure you need: Product joins with ProductWarehouse. This view just to use in Sync and then make the Sync from this view to ElasticSearch using Logstash.
Then you can use the ElasticSearsh Index as this structure:
{
"id",
"name": ...,
"warehouses": 1
}
{
"id",
"name": ...,
"warehouses": 3
}