Elasticsearch Query with self field value

Question

My elasticsearch mapping has been created to support following data structure:

"commodities" : [ { "name" : "commodity1", "displayPrice" : "price1", "prices" : [ "price" : { "type" : "price1", "amount" : "1000" }, "price" : { "type" : "price2", "amount" : "1100" } "price" : { "type" : "price3", "amount" : "1200" } ] }, { "name" : "commodity2", "displayPrice" : "price2", "prices" : [ "price" : { "type" : "price1", "amount" : "1300" }, "price" : { "type" : "price2", "amount" : "1100" } "price" : { "type" : "price3", "amount" : "1500" } ] } ]

The price object is of nested type. "displayPrice" is "not_analyzed". prices.price.type is "not_analyzed".

Now, I want to do two things here: 1. When user searches for price, the DSL query should be able to find and return the display price, e.g., if user wants to search for commodity with display price between 950 and 1150, he should get both, commodity1 and commodity2, as, for commodity1, the displayPrice is "price1" and the price.type="price1" has value 1000. 2. When user wants to sort commodity by price, the DSL should be able to sort based on the displayPrice for individual commodity.

Any help / pointers would be really appreciated.

Thanks.

==================================================================

Edited, with further details of requirements:

Thanks a lot for going through the question and preparing the code. I should have ideally done it. I believe I've mis-quoted my question. Let me re-phrase the question: I've two requirements from the given data-set:

When user searches for commodity with price range 950-1150, the system should first check what is the value for "displayPrice" and then use this value as a query to find the "price" object with that "price.type". So, e.g., for commodity1, the "displayPrice" is "price1". The corresponding "price" object under "prices" (with "price.type"="price1") has amount as "1000". So, this commodity should be returned. Similarly, for commodity2 has "displayPrice" as "price2". The corresponding "price" object in commodity2 under "prices" (with "price.type"="price2") has amount as "1100". So, this commodity should be returned.
When user sorts the commodities by price, the value for "displayPrice" should be picked. The "price" object having "price.type" as this value should be used for sorting. So, in commodity1, it should use "1000" (against "price1") and in commodity2, it should use "1100" (against "price2").

Sloan Ahrens Sloan Ahrens · Accepted Answer · 2015-09-29T15:42:34

I had to change the structure of your data around a little, but I think the following setup will give you what you want.

To test it out, I created an index with type "commodities", with a nested "prices" data structure:

PUT /test_index
{
   "mappings": {
      "commodities": {
         "properties": {
            "displayPrice": {
               "type": "string"
            },
            "name": {
               "type": "string"
            },
            "prices": {
               "type": "nested",
               "properties": {
                  "amount": {
                     "type": "integer"
                  },
                  "type": {
                     "type": "string"
                  }
               }
            }
         }
      }
   }
}

Also notice that "amount" has type "integer". Then (after adjusting amounts, and the structure of the "prices" nested objects) I indexed your two documents:

PUT /test_index/commodities/1
{
   "name": "commodity1",
   "displayPrice": "price1",
   "prices": [
      {
         "type": "price1",
         "amount": 1000
      },
      {
         "type": "price2",
         "amount": 1100
      },
      {
         "type": "price3",
         "amount": 1200
      }
   ]
}

PUT /test_index/commodities/2
{
   "name": "commodity2",
   "displayPrice": "price2",
   "prices": [
      {
         "type": "price1",
         "amount": 1300
      },
      {
         "type": "price2",
         "amount": 1100
      },
      {
         "type": "price3",
         "amount": 1500
      }
   ]
}

Now, this query seems to return what you are asking for:

POST /test_index/_search
{
   "query": {
      "filtered": {
         "filter": {
            "nested": {
               "path": "prices",
               "filter": {
                  "range": {
                     "prices.amount": {
                        "from": 950,
                        "to": 1150
                     }
                  }
               }
            }
         }
      }
   },
   "sort": [
      {
         "displayPrice": {
            "order": "desc"
         }
      }
   ]
}
...
{
   "took": 4,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "failed": 0
   },
   "hits": {
      "total": 2,
      "max_score": null,
      "hits": [
         {
            "_index": "test_index",
            "_type": "commodities",
            "_id": "2",
            "_score": null,
            "_source": {
               "name": "commodity2",
               "displayPrice": "price2",
               "prices": [
                  {
                     "type": "price1",
                     "amount": 1300
                  },
                  {
                     "type": "price2",
                     "amount": 1100
                  },
                  {
                     "type": "price3",
                     "amount": 1500
                  }
               ]
            },
            "sort": [
               "price2"
            ]
         },
         {
            "_index": "test_index",
            "_type": "commodities",
            "_id": "1",
            "_score": null,
            "_source": {
               "name": "commodity1",
               "displayPrice": "price1",
               "prices": [
                  {
                     "type": "price1",
                     "amount": 1000
                  },
                  {
                     "type": "price2",
                     "amount": 1100
                  },
                  {
                     "type": "price3",
                     "amount": 1200
                  }
               ]
            },
            "sort": [
               "price1"
            ]
         }
      ]
   }
}

If you didn't want to return the entire documents you could use the "fields" parameter, like:

POST /test_index/_search
{
    "fields": [
       "name", "displayPrice"
    ], 
   "query": {
    ...

Here is the code I used to test it:

http://sense.qbox.io/gist/8f60bcd96e27eedd0dc0af780d4e2f8bed028445

Elasticsearch Query with self field value

Edited, with further details of requirements:

1 Answers