Query Elasticsearch JSON

Question

I am trying to query elasticsearch in order to find out what products were bought with a certain product.

My data going into logstash from a flat file.

 OrderNumber ProductName
    order1   Chicken    
    order2   Banana
    order3   Chicken   
    order1   Cucumber   
    order2   Chicken 
    order3   Apples
    order1   Flour
    order2   Rice
    order3   Nuts

As you can see above i have an Product Name of Chicken which occurs in different Order Numbers.

 OrderNumber ProductName
    order1   Chicken          
    order3   Chicken
    order2   Chicken

This is what i would like to achieve :

Step 1 : Lookup all order Numbers that contain chicken

 OrderNumber ProductName
    order1   Chicken          
    order3   Chicken
    order2   Chicken

Step 2 : if the above orders have chicken in it give me all the other products that was also purchased with it

Result :

     OrderNumber   ProductName
     order1        Cucumber
     order2        Banana
     order3        Apples
     order1        Flour
     order2        Rice
     order3        Nuts

This is what i have tried so far for Step1 :

Query

{
    "query" : {
        "match" : {
        "ProductName" : "Chicken"
}
    }
      }

Result

  "hits" : {
    "total" : 3,
    "max_score" : 11.378191,
    "hits" : [ {
      "_index" : "hello",
      "_type" : "logs",
      "_id" : "AVmxaChupyZuCjD89xPX",
      "_score" : 11.378191,
      "_source" : {
        "message" : "order1\Chicken\r",
        "path" : "C:\\utils\\Elk\\logstash\\bin\\product.log",
        "OrderNumber" : "order1",
        "ProductName" : "Chicken\r"
      }}, {
      "_index" : "hello",
      "_type" : "logs",
      "_id" : "AVmxaChupyZuCjD89xPX",
      "_score" : 11.378191,
      "_source" : {
        "message" : "order3\Chicken\r",
        "path" : "C:\\utils\\Elk\\logstash\\bin\\product.log",
        "OrderNumber" : "order3",
        "ProductName" : "Chicken\r"
      }
   }, {
      "_index" : "hello",
      "_type" : "logs",
      "_id" : "AVmxaChupyZuCjD89xPX",
      "_score" : 11.378191,
      "_source" : {
        "message" : "order2\Chicken\r",
        "path" : "C:\\utils\\Elk\\logstash\\bin\\product.log",
        "OrderNumber" : "order2",
        "ProductName" : "Chicken\r"
      }

I'm very confused as to how to go about completing step 2 as I am very new to Elasticsearch, please help ?

Thanks

Answer 1

To search for documents that have order numbers found for Chicken and product names other than Chicken, you can use the following query:

{
  "query" : {
    "and" : [
       {
         "terms" : {
           "OrderNumber" : ["order1", "order2", "order3"]
         }
       },
       {
         "not" : {
           "term" : {
             "ProductName" : "Chicken"
           }
         }
       }
    ]
  }
}