2

I am working on a project concerning cooking recipes. I installed Elasticsearch 1.5.2 and I added a lot of products such as vegetables or meats in many indexes of supermarkets. All was well until I started aggregations queries. For example: to make a mash potatoes I need potatoes, beans, chickpeas, broccoli, milk, pepper, salt. I have all this products stored. I need to make one query to search the cheapest of this products in all indexes. I tried many queries but I didn't find what I need.

This is the example, all these queries works but I need their results in one query:

 POST /_search
    {
        "query": {
            "query_string": {
               "query": "pommes de terre",
               "fields": [
                  "titre"
               ]
            }
        },
       "sort" : [
          {"prix en €/kg" : {"order" : "asc"}}
       ]
    }

    POST /_search
    {
        "query": {
            "query_string": {
               "query": "haricots",
               "fields": [
                  "titre"
               ]
            }
        },
       "sort" : [
          {"prix en €/kg" : {"order" : "asc"}}
       ]
    }

    POST /_search
    {
        "query": {
            "query_string": {
               "query": "pois chiche",
               "fields": [
                  "titre"
               ]
            }
        },
       "sort" : [
          {"prix en €/kg" : {"order" : "asc"}}
       ]
    }

    POST /_search
    {
        "query": {
            "query_string": {
               "query": "brocoli",
               "fields": [
                  "titre"
               ]
            }
        },
       "sort" : [
          {"prix en €/kg" : {"order" : "asc"}}
       ]
    }

    POST /_search
    {
        "query": {
            "query_string": {
               "query": "lait",
               "fields": [
                  "tags"
               ]
            }
        },
       "sort" : [
          {"prix en €/L" : {"order" : "asc"}}
       ]
    }

    POST /_search
    {
        "query": {
            "query_string": {
               "query": "poivre",
               "fields": [
                  "tags"
               ]
            }
        },
       "sort" : [
          {"prix en €/kg" : {"order" : "asc"}}
       ]
    }

    POST /_search
    {
        "query": {
            "query_string": {
               "query": "sel",
               "fields": [
                  "tags"
               ]
            }
        },
       "sort" : [
          {"prix en €/kg" : {"order" : "asc"}}
       ]
    }

I want to have only one query to fetch the results of all these queries and I only want the cheapest ones, not all the lists.

Sofien.011
  • 39
  • 8

1 Answers1

0

You could define a filter aggregation for each product in your query

{
    "aggs" : {
        "sel" : {
            "filter" : {
                "query_string": {
                   "query": "sel",
                   "fields": [
                      "titre"
                   ]
                }
            }
        },
        "haricots" : {
            "filter" : {
                "query_string": {
                   "query": "haricots",
                   "fields": [
                      "titre"
                   ]
                }
            }
        }
    }
}

Note that on ES<2, you probably will have to wrap your filter in a query object:

{
    "aggs" : {
        "sel" : {
            "filter" : {
                "query": {
                    "query_string": {
                       "query": "sel",
                       "fields": [
                          "titre"
                       ]
                    }
                }
            }
        },
        "haricots" : {
            "filter" : {
                "query": {
                    "query_string": {
                       "query": "haricots",
                       "fields": [
                          "titre"
                       ]
                    }
                }
            }
        }
    }
}

Each aggregation build a bucket corresponding your filters. You would then add a top hits sub aggregation to each of these buckets to get the lowest price. For example, with the sel bucket :

{
    "aggs" : {
        "sel" : {
            "filter" : {
                "query_string": {
                   "query": "sel",
                   "fields": [
                      "titre"
                   ]
                }
            },
            "aggs" : {
                "minprice": {
                    "top_hits": {
                        "sort": [
                            {
                                "prix en €/kg": {
                                    "order": "asc"
                                }
                            }
                        ],
                        "size" : 1
                    }
                }
            }
        }
    }
}

And a fuller example with sel, brocoli and haricots

{
    "aggs" : {
        "sel" : {
            "filter" : {
                "query_string": {
                   "query": "sel",
                   "fields": [
                      "titre"
                   ]
                }
            },
            "aggs" : {
                "minprice": {
                    "top_hits": {
                        "sort": [
                            {
                                "prix en €/kg": {
                                    "order": "asc"
                                }
                            }
                        ],
                        "size" : 1
                    }
                }
            }
        },
        "haricots" : {
            "filter" : {
                "query_string": {
                   "query": "haricots",
                   "fields": [
                      "titre"
                   ]
                }
            },
            "aggs" : {
                "minprice": {
                    "top_hits": {
                        "sort": [
                            {
                                "prix en €/kg": {
                                    "order": "asc"
                                }
                            }
                        ],
                        "size" : 1
                    }
                }
            }
        },
        "brocoli" : {
            "filter" : {
                "query_string": {
                   "query": "brocoli",
                   "fields": [
                      "titre"
                   ]
                }
            },
            "aggs" : {
                "minprice": {
                    "top_hits": {
                        "sort": [
                            {
                                "prix en €/kg": {
                                    "order": "asc"
                                }
                            }
                        ],
                        "size" : 1
                    }
                }
            }
        }
    }
}

Your results would look like

{
    "aggregations" : {
        "sel" : {
            "doc_count" : 2,
            "minprice" : {
                "hits" : {
                    "total" : 2,
                    "max_score" : null,
                    "hits" : [
                        {
                            "_index" : "test",
                            "_type" : "product",
                            "_id" : "1",
                            "_score" : null,
                            "_source" : {
                                "id" : 1,
                                "titre" : "sel 1",
                                "prix en €/kg" : 1
                            },
                            "sort" : [
                                1.0
                            ]
                        }
                    ]
                }
            }
        },
        "haricots" : {
            "doc_count" : 1,
            "minprice" : {
                "hits" : {
                    "total" : 1,
                    "max_score" : null,
                    "hits" : [
                        {
                            "_index" : "test",
                            "_type" : "product",
                            "_id" : "3",
                            "_score" : null,
                            "_source" : {
                                "id" : 3,
                                "titre" : "haricots",
                                "prix en €/kg" : 3
                            },
                            "sort" : [
                                3.0
                            ]
                        }
                    ]
                }
            }
        }
    }
}
Community
  • 1
  • 1
nikoshr
  • 32,926
  • 33
  • 91
  • 105
  • Thank you. But, I have this error : "SearchPhaseExecutionException[Failed to execute phase [query], all shards failed; shardFailures {[tZ-AvbvUQAaemOy2BSk_2Q][testindex][0]: SearchParseException[[testindex][0]: from[-1],size[-1]: Parse Failure [Failed to parse source.......] " – Sofien.011 Feb 01 '17 at 14:11
  • Maybe a transposition problem : check that the names of the fields and indexes are indeed what you have in your server. – nikoshr Feb 01 '17 at 14:21
  • Should I add for example "sel" in all my indexes to make this works? {"sel" : { "filter" : { "query_string": { "query": "sel", "fields": [ "titre" ] } }, "aggs" : { "minprice": { "top_hits": { "sort": [ { "prix en €/kg": { "order": "asc" } } ], "size" : 1 } } } } } – Sofien.011 Feb 01 '17 at 14:25
  • No, the first and second code blocks are missing a few elements to work as is, they are meant as an explanation. Only the third example should work as is. I'll add the necessary wrappings, in the meantime, please try the third code block. – nikoshr Feb 01 '17 at 14:29
  • I am working on localhost. I Put in the beginning "POST /_search?search_type=count" So it searchs in all indexes. Even when i specify the index, I still have the same error. – Sofien.011 Feb 01 '17 at 14:31
  • Any of the 3 examples should work as is now. Write an example in a file `test.json` and send that with `curl http://localhost:9200/_search?pretty --data-binary @test.json` – nikoshr Feb 01 '17 at 14:33
  • I tried it and i had the same long error. I copy to you only the two first lines. – Sofien.011 Feb 01 '17 at 14:35
  • It would seem ES1.5 requires a bit more wrapping. I've added a correction for your version of ES (on the first example, the rest would have to be modified accordingly: add a `query` object around `query_string`). – nikoshr Feb 01 '17 at 16:28
  • Now, I can go ahead! thank you for helping me, I really appreciate it. – Sofien.011 Feb 01 '17 at 21:16