# Designing an Index with high Cardinality for a field distributed across multiple shards

**URL:** <https://discuss.elastic.co/t/designing-an-index-with-high-cardinality-for-a-field-distributed-across-multiple-shards/374302>\
**Category:** Elasticsearch\
**Created:** [February 10, 2025, 10:13am UTC](https://discuss.elastic.co/t/designing-an-index-with-high-cardinality-for-a-field-distributed-across-multiple-shards/374302 "2025-02-10T10:13:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jatin\_Garg1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jatin_garg1/32/141259_2.png) [@Jatin\_Garg1](https://discuss.elastic.co/u/Jatin_Garg1)\
**Post date:** [February 10, 2025, 10:13am UTC](https://discuss.elastic.co/t/designing-an-index-with-high-cardinality-for-a-field-distributed-across-multiple-shards/374302/1 "2025-02-10T10:13:44Z")

</div>

Hello,

I am designing an index for logs related to some search API which has information like type/count of searches AND type/count of results served in a search response.

Sample Information in the Log:  
{  
SearchId: "12345678",  
SearchLocation: "India",  
Results: [  
{ position: 1,  
result\_accuracy: "high"  
},  
{ position: 2,  
result\_accuracy: "medium"  
}  
]  
}

For this information, I want to create an index to visualize the following in Kibana:

**Use Cases:**

1. Count of Searches.
2. Count of Results with medium accuracy at position 1.
3. Count of Searches with medium accuracy results at position 1.

**Approach**

1. Can't use Object type field for "results" because it will serve false information for use case 2 and 3.
2. Can't use nested/parent-child field types for results because it is not yet supported in Kibana visualization.
3. One possible solution could be to flatten the results and add search related information in each result doc Like:

{  
resultId: "87654"  
SearchId: "12345678",  
SearchLocation: "India",  
position: 1,  
result\_accuracy: "high"  
}  
{  
resultId: "234"  
SearchId: "12345678",  
SearchLocation: "India",  
position: 2,  
result\_accuracy: "medium"  
}

How Use Cases will be solved:

1. Count of Searches with some filtering criteria --\> **Count Unique of SearchId**
2. Count of Results with medium accuracy at position 1 --\> **Count of docs with filtering on result\_accuracy and position**
3. Count of Searches with medium accuracy results at position 1 --\> **Count Unique of SearchId with some filters**

Now, this field SearchId will have high cardinality. (for 2 million searches per day, and 10 results per search --\> 20 million docs with 2 million unique SearchIds)

Challenge 1  
**Some percentage error with count unique in Cardinality aggregation**

Challenge 2  
**Now when these result documents are distributed across multiple shards, then the no of unique searches will be inflated because unqiue counts will be calculated wrt each shard**

Any help or validation around designing the same?

---

<div class="post-metadata">

**Author:** ![Carlos\_D](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carlos_d/32/126245_2.png) [@Carlos\_D](https://discuss.elastic.co/u/Carlos_D)\
**Post date:** [February 13, 2025, 8:27am UTC](https://discuss.elastic.co/t/designing-an-index-with-high-cardinality-for-a-field-distributed-across-multiple-shards/374302/2 "2025-02-13T08:27:56Z")

</div>

hey @Jatin_Garg1 :

I believe using the [nested field type](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) would help addressing points 2 and 3, something like:

```json
{
  "mappings": {
    "properties": {
      "search_id": "unsigned_long",
      "search_location": "keyword",
      "results": {
        "type": "nested",
        "properties": {
          "position": "integer",
          "accuracy": "keyword"
        }
      }
    }
  }
}

```

That way, you'll be able to filter using a [nested query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html):

```json
{
  "query": {
    "nested": {
      "path": "results",
      "query": {
        "bool": {
          "filter": [
            { "match": { "results.position": 1 } },
            { "match": { "results.accuracy": "medium" } }
          ]
        }
      }
    }
  }
}

```

That query will effectively match against any document that contains an element in the `results` array that matches the query.

I hope that helps!

---

<div class="post-metadata">

**Author:** ![Jatin\_Garg1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jatin_garg1/32/141259_2.png) [@Jatin\_Garg1](https://discuss.elastic.co/u/Jatin_Garg1)\
**Post date:** [February 13, 2025, 9:17am UTC](https://discuss.elastic.co/t/designing-an-index-with-high-cardinality-for-a-field-distributed-across-multiple-shards/374302/3 "2025-02-13T09:17:09Z")

</div>

Hi @Carlos_D,

The end goal of the index is to visualize these on KIbana.  
Nested Fields aren't supported on Kibana yet

> <https://github.com/elastic/kibana/issues/1084>
>
> This is sort of a duplicate of some other issues I searched for but I haven't se…en this particular aspect discussed, so I thought this was worth a separate issue.
> 
> You read the \_mapping field, so you should know when a particular field is nested, so can it not automatically apply the correct nested facet/query when such a field is selected in queries or facets? 
> 
> (Alternatively/in addition as suggested by #532, you could have a checkbox to allow users to select it themselves, perhaps as an interim measure)
> 
> I'm sure there are some cases where this gets complicated, but there are also a bunch of cases where it's a straightforward changing of one block of JSON to another.
> 
> 
> Latest update: https://github.com/elastic/kibana/issues/1084#issuecomment-585178079

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [February 13, 2025, 9:41am UTC](https://discuss.elastic.co/t/designing-an-index-with-high-cardinality-for-a-field-distributed-across-multiple-shards/374302/4 "2025-02-13T09:41:10Z")

</div>

It sounds to me like you want to search and analyse 2 different things - searches and results. One way to do this without requiring parent-child or nested mappings (not supported by Kibana) might be to create 2 separate indices, one for `searches` and one for `results`.

The `searches` index might look something like this:

```auto
{
  "SearchId": "12345678",
  "SearchLocation": "India",
  "Results": ["1 high", "2 medium"]
}

```

Here I concatenated each result into a single string, which would be analyzed using a whitespace analyzer as well as mapped as a keyword field. You can use different types of queries depending on what you are looking for.

In the `results` index you would index each result separately with the search information denormalised as in your example:

```auto
{
  "resultId": "87654"
  "SearchId": "12345678",
  "SearchLocation": "India",
  "position": 1,
  "result_accuracy": "high"
}

```

Use cases 1 and 3 would use the `searches` index while use case 2 would run against the `results` index.

---

<div class="post-metadata">

**Author:** ![Carlos\_D](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carlos_d/32/126245_2.png) [@Carlos\_D](https://discuss.elastic.co/u/Carlos_D)\
**Post date:** [February 13, 2025, 9:42am UTC](https://discuss.elastic.co/t/designing-an-index-with-high-cardinality-for-a-field-distributed-across-multiple-shards/374302/5 "2025-02-13T09:42:06Z")

</div>

Oh I see, sorry about that! I thought the question was about index design and missed the Kibana requirement ☹

Some alternatives for this have been discussed [here](https://discuss.elastic.co/t/how-to-analyse-nested-fields/325944/2) - basically using a flattened structure, or using a custom Vega visualization over a nested query.