Sum by quantity and sort by lowest price

79g · August 4, 2022, 8:16am

try these searches:

For the first query:

GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ]
}

Second query:

GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ],
  "aggs": {
    "lead_time_avg": {
      "avg": {
        "field": "lead_time"
      }
    },
    "total_price": {
      "sum": {
        "script": {
          "lang": "painless",
          "inline": "doc['price'].value * doc['quantity'].value"
        }
      }
    }
  }
}

Used data:

PUT /discuss
PUT /discuss/_mapping
{
  "properties": {
    "description": {
      "type": "text"
    },
    "quantity": {
      "type": "double"
    },
    "price": {
      "type": "double"
    },
    "lead_time": {
      "type": "double"
    }
  }
}

POST discuss/_doc/
{
  "description": "Product 1",
  "quantity": "200",
  "price": "4",
  "lead_time": "2"
}
POST discuss/_doc/
{
  "description": "Product 2",
  "quantity": "150",
  "price": "3",
  "lead_time": "5"
}
POST discuss/_doc/
{
  "description": "Product 3",
  "quantity": "275",
  "price": "5",
  "lead_time": "14"
}

GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ]
}

I attach you also the output:

Hope it helps