Elasticsearch how to sort with condition

Question

On my ElasticSearch (2.x) I have documents like this:

{
    "title": "A good title",
    "formats": [{
        "name": "pdf",
        "prices": [{
            "price": 11.99,
            "currency": "EUR"
        }, {
            "price": 18.99,
            "currency": "AUD"
        }]
    }]
}

I'd like to sort documents by formats.prices.price but only where the formats.prices.currency === 'EUR'

I tried to do a nested field on formats.prices and then run this query:

{
  "query": {
    "filtered": {
      "query": {
        "and": [
          {
            "match_all": {}
          }
        ]
      }
    }
  },
  "sort": {
    "formats.prices.price": {
      "order": "desc",
      "nested_path": "formats.prices",
      "nested_filter": {
        "term": {
          "currency": "EUR"
        }
      }
    }
  }
}

But unfortunately I cannot get the right order.

UPDATE: Relevant part of mapping:

   "formats": {
        "properties": {
          "name": {
            "type": "string"
          },
          "prices": {
            "type": "nested",
            "include_in_parent": true,
            "properties": {
              "currency": {
                "type": "string"
              },
              "price": {
                "type": "double"
              }
            }
          }
        }
      },

blackmamba · Accepted Answer · 2016-09-02 05:29:42Z

1

i hope this will solve your problem

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "formats.prices",
            "filter": {
              "match": {
                "formats.prices.currency": "EUR"
              }
            }
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 50,
  "sort": [
    {
      "formats.prices.price": {
        "order": "asc",
        "nested_path": "formats.prices",
        "nested_filter": {
          "match": {
            "formats.prices.currency": "EUR"
          }
        }
      }
    }
  ]
}

answered Sep 2, 2016 at 5:29

blackmamba

5563 silver badges11 bronze badges

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

7 Comments

michelem Over a year ago

Nope. It seems it rewrites all the currency to 'EUR' and the order isn't correct.

blackmamba Over a year ago

can you give me your mapping ?

blackmamba Over a year ago

"properties": { "formats": { "type": "nested", "properties": { "name": { "type": "string" }, "prices": { "type": "nested", "properties": { "price": { "type": "double" }, "currency": { "type": "string" } } } } }, "title": { "type": "string" } }

blackmamba Over a year ago

something like this ? it's working for this one i have checked

michelem Over a year ago

Mmmm what is changing is formats is not nested in my mapping, should I change it to nested too?

|

Collectives™ on Stack Overflow

Elasticsearch how to sort with condition

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related