Correct way of mapping some structure to Elasticsearch document

Good day, community!
At this moment we're posting some items from our solution to the ES index. Everything works fine. But we want to enlarge our decision. Our project gives ability to categorize items. We want to have this information present in Elasticsearch documents.
Some words about items and categorizing. Each item could be put in some category with specified sequence. For exmple, if item is pencil, it could be categorized by size and color. So, there will be 2 main categories - Size and Color, each of them will have sub-categories (for example, Red, Green, Blue for Color). Some item will be present in several categories according to the "properties" of the pencil. For example, green 6mm pencil will be present in categories "Green" and "6mm".
Each category has it's own ID. So, we want to have this information presented in ES document. What we need: list of category IDs where some item is present. And additionally we need to have sequence numbers of current item in that categories. Our general idea was to add 2 properties for document - category_ids and category_sequences. Everything is fine with first list. But second one contains pairs of values - "category_id": "sequence":

            "category_ids" : [
            "category_sequences" : {
              "100000000001" : 4,
              "100000000003" : 1

This approach is not work correctly, because for "category_sequences" property each category_id value (for example, "100000000001") is treated by Elastic as property name. Elastic has limit for unique property names equal to 1000 and it broke our idea.
So, my question is - what is best way to map this data on the ES document properties?
Some words about typical requests that we want to proceed with this data:

  1. get all items from specified category (for example, return all green pencils - pencils from category Green)
  2. return all items from some category, ordered by sequence numbers
    I understand, that we can put this data as array of nested objects in the way like this:
            "category_data" : {
              "category_id": 100000000001,
              "sequence":  4
              "category_id": 100000000003,
              "sequence":  1

But this solution will push us to use nested queries. I think that such decision will slow our queries and make our query structure much harder to analyze.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.