Elasticsearch: Aggregate distinct values in array

Question

I am using Elasticsearch to store click traffic and each row includes topics of the page which has been visited. A typical row looks like:

{
  "date": "2017-09-10T12:26:53.998Z",
  "pageid": "10263779",
  "loc_ll": [
    -73.6487,
    45.4671
  ],
  "ua_type": "Computer",
  "topics": [
    "Trains",
    "Planes",
    "Electric Cars"
  ]
}

I want each topics to be a keyword so if I search for cars nothing will be returned. Only Electric Cars would return a result.

I also want to run a distinct query on all topics in all rows so I have a list of all topics used.

Doing this on a pageid would look like like the following, but I am unsure how to approach this for the topics array.

{
  "aggs": {
    "ids": {
      "terms": {
        "field": pageid,
        "size": 10
      }
    }
  }
}

Andreas Jägle · Accepted Answer · 2017-09-10 16:25:57Z

6

Your approach to querying and getting the available terms looks fine. Probably you should check your mapping. If you get results for cars this looks as your mapping for topics is an analyzed string (e.g. type text instead of keyword). So please check your mapping for this field.

PUT keywordarray
{
  "mappings": {
    "item": {
      "properties": {
        "id": {
          "type": "integer"
        },
        "topics": {
          "type": "keyword"
        }
      }
    }
  }
}

With this sample data

POST keywordarray/item
{
  "id": 123,
  "topics": [
    "first topic", "second topic", "another"
  ]
}

and this aggregation:

GET keywordarray/item/_search
{
  "size": 0,
  "aggs": {
    "topics": {
      "terms": {
        "field": "topics"
      }
    }
  }
}

will result in this:

"aggregations": {
  "topics": {
    "doc_count_error_upper_bound": 0,
    "sum_other_doc_count": 0,
    "buckets": [
      {
        "key": "another",
        "doc_count": 1
      },
      {
        "key": "first topic",
        "doc_count": 1
      },
      {
        "key": "second topic",
        "doc_count": 1
      }
    ]
  }
}

answered Sep 10, 2017 at 16:25

Andreas Jägle

12.4k3 gold badges33 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dmo Over a year ago

keyword was it, I was mapping it as text thinking that keyword for concatenate the array.

dmo · Accepted Answer · 2017-09-10 16:21:35Z

1

It is very therapeutic asking on SO. Simply changing the mapping type to keyword allowed me to achieve what I needed.

A part of me thought that it would concatenate the array into a string. But it doesn't

{
  "mappings": {
    "view": {
      "properties": {
        "topics": {
          "type": "keyword"
        },...
      }
    }
  }
}

and a search query like

{
  "aggs": {
    "ids": {
      "terms": {
        "field": pageid,
        "size": 10
      }
    }
  }
}

Will return a distinct list of all elements in a fields array.

answered Sep 10, 2017 at 16:21

dmo

5,3713 gold badges27 silver badges28 bronze badges

Collectives™ on Stack Overflow

Elasticsearch: Aggregate distinct values in array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related