Unable to retrieve nested object within Elastic Search

Question

An ELK noob here, having the ELK task drop to me last minute.

We are adding an extra data named prospects into the vehicle index, so the user could search for it. I'm able to to add the prospects into the index, now I'm unable to get the nested prospects obj within the vehicle index. I'm using Elastic Search & Kibana v6.8.11, and elastic-search-rails gem and checked up the docs on nested object. My search method looks correct according to the docs. Would like some expert to point out what when wrong here, please let me know if you need more info.

Here is the suppose index obj -

      {
        "_index" : "vehicles",
        "_type" : "_doc",
        "_id" : "3MZBxxxxxxx",
        "_score" : 0.0,
        "_source" : {
          "vin" : "3MZBxxxxxxx",
          "make" : "mazda",
          "model" : "mazda3",
          "color" : "unknown",
          "year" : 2018,
          "vehicle" : "2018 mazda mazda3",
          "trim" : "grand touring",
          "estimated_mileage" : null,
          "dealership" : [
            209
          ],
          "current_owner_group_id" : null,
          "current_owner_customer_id" : null,
          "last_service_date" : null,
          "last_service_revenue" : null,
          "purchase_type" : [ ],
          "in_service_date" : null,
          "deal_headers" : [ ],
          "services" : [ ],
          "customers" : [ ],
          "salesmen" : null,
          "service_appointments" : [ ],
          "prospects" : [
            {
              "first_name" : "Kammy",
              "last_name" : "Maytag",
              "name" : "Kammy Maytag",
              "company_name" : null,
              "emails" : [ ],
              "phone_numbers" : [ ],
              "address" : "31119 field",
              "city" : "helen",
              "state" : "keller",
              "zip" : "81411",
              "within_dealership_aoi_region" : true,
              "dealership_ids" : [
                209
              ],
              "dealership_dppa_protected_ids" : [
                209
              ],
              "registration_id" : 12344,
              "id" : 1054,
              "prospect_source_id" : "12344",
              "type" : "Prospect"
            }
          ]
        }
      }
    ]
  }
}

Here is how I'm trying to get it -

 GET /vehicles/_search
{
  "query": {
    "bool": {
      "must": { "match_all": {} },
      "filter": [
        { "term": { "dealership": "209" } },
        {
          "nested": {
            "path": "prospects",
            "query": {
              "bool": {
                "must": [
                  { "term": { "prospects.first_name": "Kammy" } },
                  { "term": { "prospects.dealership": "209" } },
                  { "term": { "prospects.type": "Prospect" } }
                ]
              }
            }
          }
        },
        { "bool": { "must_not": { "term": { "purchase_type": "Wholesale" } } } }
      ]
    }
  },
  "sort": [{ "_doc": { "order": "asc" } }]
}

Answer 1

I see two issues with the nested query:

1. You're querying prospects.dealership but the example doc only shows prospects.dealership_ids. Change query to target prospects.dealership_ids.
2. More importantly, you're using a term query on prospects.first_name and prospects.type. I'm assuming your index mapping doesn't define those as keywords which means that they were most likely lowercased (for reasons explained here) but term is looking for exact matches.
   • Option 1: Use match instead of term.
   • Option 2: Change prospects.first_name → prospects.first_name.keyword and do the same for .type.