How to write the elastic search query for this?

Question

How do we write a query with following conditions:

1. status MUST NOT be "INACTIVE"
2. searchString MUST be "some_String"
3. adsPurchased MUST to be true
4. must have "Product 1" under productCost
5. Sort them based on cost of "Product 1"

Data structure:

  "name": "Ross",
  "status": "ACTIVE",
  "adsPurchased": true,
  "searchString": "some_tring",
  "productCost": [
    {
      "product": "Product 1",
      "cost": "50.0"
    },
    {
      "product": "Product 2",
      "cost": "80.0"
    }
  ]
}

{
  "name": "Chandler",
  "status": "INACTIVE",
  "adsPurchased": true,
  "searchString": "some_String",
  "productCost": [
    {
      "product": "Product 1",
      "cost": "60.0"
    },
    {
      "product": "Product 4",
      "cost": "800.0"
    }
  ]
}

{
  "name": "joey",
  "status": "ACTIVE",
  "adsPurchased": true,
  "searchString": "some_tring",
  "productCost": [
    {
      "product": "Product 1",
      "cost": "30.0"
    },
    {
      "product": "Product 5",
      "cost": "90.0"
    }
  ]
}


So, I should get Ross and Joey

Answer 1

Mapping:

{
  "mappings": {
    "properties": {
      "name": {
        "type": "text"
      },
      "status": {
        "type": "text"
      },
      "adsPurchased": {
        "type": "boolean"
      },
      "searchString": {
        "type": "text"
      },
      "productCost": {
        "type": "nested",
        "properties": {
          "product": {
            "type": "text"
          },
          "cost": {
            "type": "integer"
          }
        }
      }
    }
  }
}

Search Query:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "adsPurchased": true    -->adsPurchased MUST to be true
          }
        },
        {
          "match": {
            "searchString": "some_tring"    -->searchString MUST be "some_tring"
          }
        },
        {
          "nested": {
            "path": "productCost",
            "query": {
              "bool": {
                "must": [           -->must have "Product 1" under productCost
                  {
                    "match": {
                      "productCost.product": "Product 1"  
                    }
                  }
                ]
              }
            }
          }
        }
      ],
      "must_not": [
        {
          "match": {
            "status": "INACTIVE"    -->status MUST NOT be "INACTIVE"              
            }
        }
      ]
    }
  },
  "sort": [                                 -->Sort them based on cost
    {
      "productCost.cost": {
        "order": "asc",
        "nested_path": "productCost"
      }
    }
  ]
}

Search Result :

"hits": [
      {
        "_index": "foo3",
        "_type": "_doc",
        "_id": "2",
        "_score": null,
        "_source": {
          "name": "joey",
          "status": "ACTIVE",
          "adsPurchased": true,
          "searchString": "some_tring",
          "productCost": [
            {
              "product": "Product 1",
              "cost": "30.0"
            },
            {
              "product": "Product 5",
              "cost": "90.0"
            }
          ]
        },
        "sort": [
          30
        ]
      },
      {
        "_index": "foo3",
        "_type": "_doc",
        "_id": "1",
        "_score": null,
        "_source": {
          "name": "Ross",
          "status": "ACTIVE",
          "adsPurchased": true,
          "searchString": "some_tring",
          "productCost": [
            {
              "product": "Product 1",
              "cost": "50.0"
            },
            {
              "product": "Product 2",
              "cost": "80.0"
            }
          ]
        },
        "sort": [
          50
        ]
      }
    ]

In the search result, you get your desired result i.e Ross and joey

To know more about nested sorting, you can refer to this official documentation and for nested queries refer this