Query to get data related to only those field returned by aggregation


(Nikhil Nambiar) #1

Hi,
I need a help on the query where I want to search by phone numbers and get latest date amount charged for that phone number
my mapping structure is as follows

    "mappings":{
	"phone_list":{
		"properties":{
			"phone":{
				"type":"text"
			}
			"amount":{
				"type":"integer"
			}
			"date_timestamp":{
				"type":"text"
			}
			
		}
	}
}

I have performed a query which gives the latest date_timestamp of a particular phone number . As follows

   {
      "query":{
    "match":{
          "phone":"1234567890"
        }
      },
      "aggs":{
          "latest_call_date&time":{
        "max":{
              "field":"date_timestamp"
            }
          }
    }
    }

Now how can I achieve the amount related to the date_timestamp returned by aggregation function max.


(Abdon Pijpelink) #2

Do you really need to use an aggregation? I think it's simpler to just sort the search results on date_timestamp and ask Elasticsearch for 1 hit. That one hit will then tell you everything you want to know about the most recent transaction for a given phone number.

GET test/_search
{
  "size": 1,
  "query": {
    "match": {
      "phone": "1234567890"
    }
  },
  "sort": [
    {
      "date_timestamp": {
        "order": "desc"
      }
    }
  ]
}

(By the way, I think date_timestamp should probably be mapped as a date?)


(Nikhil Nambiar) #3

Hi Abdon,

I had resolved the query, the same way as you mentioned.

For sake of curiosity can we achieve the same by aggregation?


(Abdon Pijpelink) #4

Maybe something with a top hits or a max bucket aggregation could get you to what you need.


(system) #5

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