CODEWITHSUNDEEP
elasticsearchdata-modelingopensearch
anon_from_uk
anon_from_uk

Reputation: 23

OpenSearch / ElasticSearch index mappings

I have a system that ingests multiple scores for events and we use opensearch (previously elastic search) for getting the averages.

For example, an input would be similar to:

// event 1
{
  id: "foo1",
  timestamp: "some-iso8601-timestamp",
  scores: [
    { name: "arbitrary-name-1", value: 80 },
    { name: "arbitrary-name-2", value: 55 },
    { name: "arbitrary-name-3", value: 30 },
  ]
}

// event 2
{
  id: "foo2",
  timestamp: "some-iso8601-timestamp",
  scores: [
    { name: "arbitrary-name-1", value: 90 },
    { name: "arbitrary-name-2", value: 65 },
    { name: "arbitrary-name-3", value: 40 },
  ]
}

The score name are arbitrary and subject to change from time to time.

We ultimately would like to query the data to get the average scores values:

[
  { name: "arbitrary-name-1", value: 85 },
  { name: "arbitrary-name-2", value: 60 },
  { name: "arbitrary-name-3", value: 35 },
]

However, the only way we have been able to achieve this so far has been to insert multiple documents, one for each score name/value pair in each event. This seems wasteful. The search in place currently is to group the documents by score name and timestamp intervals, then to perform a weighted average of the scores in each bucket.

Is there a way the data can be inserted to allow this query pattern to take place by only adding one document into opensearch per event/record (rather than one document per score per event/record)? How might that look?

Thanks!

Upvotes: 0

Views: 1115

Answers (1)

Paulo
Paulo

Reputation: 10746

Is it what you were trying to do ? I got a bit confused. ^^

DELETE /71397606

PUT /71397606
{
  "mappings": {
    "properties": {
      "id": {
        "type": "text"
      },
      "scores": {
        "type": "nested",
        "properties": {
          "name": {
            "type": "keyword"
          },
          "value": {
            "type": "long"
          }
        }
      },
      "timestamp": {
        "type": "text"
      }
    }
  }
}

POST /_bulk
{"index":{"_index":"71397606"}}
{"id":"foo1","timestamp":"some-iso8601-timestamp","scores":[{"name":"arbitrary-name-1","value":80},{"name":"arbitrary-name-2","value":55},{"name":"arbitrary-name-3","value":30}]}
{"index":{"_index":"71397606"}}
{"id":"foo2","timestamp":"some-iso8601-timestamp","scores":[{"name":"arbitrary-name-1","value":90},{"name":"arbitrary-name-2","value":65},{"name":"arbitrary-name-3","value":40}]}
{"index":{"_index":"71397606"}}
{"id":"foo2","timestamp":"some-iso8601-timestamp","scores":[{"name":"arbitrary-name-1","value":85},{"name":"arbitrary-name-x","value":65},{"name":"arbitrary-name-y","value":40}]}

GET /71397606/_search
{
  "size": 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "nested": {
      "nested": {
        "path": "scores"
      },
      "aggs": {
        "pername": {
          "terms": {
            "field": "scores.name",
            "size": 10
          },
          "aggs": {
            "avg": {
              "avg": {
                "field": "scores.value"
              }
            }
          }
        }
      }
    }
  }
}

ps: If not could you give an example ?

Upvotes: 0

Related Questions