Opensearch index mapping for single or dual

OpenSearch
, ,
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):

:wave::skin-tone-2: Hello, team!
I’m working on structuring the search index for millions of items, each with available quantities at the fulfillment-center level. I’m evaluating approaches that preserve full filtering and sorting capabilities on all indexed fields with affecting performance
I’d appreciate your thoughts on whether a single index or separate indexes for item data and fulfillment-center inventory would be more appropriate.

Configuration:

Relevant Logs or Screenshots:

2

@Kerry For this use case you could use Parent/child in one index:

Use a join field: parent = item, child = inventory-per-FC (fulfillment-center). Query with has_child/has_parent; route children to the parent shard. However note that join queries are slower and search.allow_expensive_queries=true is required to execute joint queries.

See example below:

PUT items_pc
{
  "mappings": {
    "properties": {
      "relation": {
        "type": "join",
        "relations": { "item": "stock" }
      },
      "sku": { "type": "keyword" },
      "name": { "type": "text" },
      "fc_id": { "type": "keyword" },
      "qty": { "type": "integer" }
    }
  }
}

# parent docs (items) - note _routing = item id
PUT items_pc/_doc/sku-1?routing=sku-1
{ "relation": "item", "sku": "sku-1", "name": "Widget" }

PUT items_pc/_doc/sku-2?routing=sku-2
{ "relation": "item", "sku": "sku-2", "name": "Gadget" }

# child docs (stock per FC) - relation object + same routing as parent, must be on the same shard
PUT items_pc/_doc/sku-1-fc-a?routing=sku-1
{ "relation": { "name": "stock", "parent": "sku-1" }, "fc_id": "FC-A", "qty": 15 }

PUT items_pc/_doc/sku-1-fc-b?routing=sku-1
{ "relation": { "name": "stock", "parent": "sku-1" }, "fc_id": "FC-B", "qty": 0 }

PUT items_pc/_doc/sku-2-fc-a?routing=sku-2
{ "relation": { "name": "stock", "parent": "sku-2" }, "fc_id": "FC-A", "qty": 7 }

# Query items that have any stock > 0 in FC-A
POST items_pc/_search
{
  "query": {
    "has_child": {
      "type": "stock",
      "query": { "bool": { "must": [ { "term": { "fc_id": "FC-A" } }, { "range": { "qty": { "gt": 0 } } } ] } },
      "score_mode": "min"
    }
  }
}

# Update one FC’s stock without touching parent
POST items_pc/_update/sku-1-fc-a?routing=sku-1
{ "doc": { "qty": 3 } }