Elasticsearch query fails to return results when querying a nested object

Question

I have an object which looks something like this:

{
  "id": 123,
  "language_id": 1,
  "label": "Pablo de la Pena",
  "office": {
    "count": 2,
    "data": [
      {
        "id": 1234,
        "is_office_lead": false,
        "office": {
          "id": 1,
          "address_line_1": "123 Main Street",
          "address_line_2": "London",
          "address_line_3": "",
          "address_line_4": "UK",
          "address_postcode": "E1 2BC",
          "city_id": 1
        }
      },
      {
        "id": 5678,
        "is_office_lead": false,
        "office": {
          "id": 2,
          "address_line_1": "77 High Road",
          "address_line_2": "Edinburgh",
          "address_line_3": "",
          "address_line_4": "UK",
          "address_postcode": "EH1 2DE",
          "city_id": 2
        }
      }
    ]
  },
  "primary_office": {
    "id": 1,
    "address_line_1": "123 Main Street",
    "address_line_2": "London",
    "address_line_3": "",
    "address_line_4": "UK",
    "address_postcode": "E1 2BC",
    "city_id": 1
  }
}

My Elasticsearch mapping looks like this:

"mappings": {
  "item": {
    "properties": {
      "office": {
        "properties": {
          "data": {
            "type": "nested",
          }
        }
      }
    }
  }
}

My Elasticsearch query looks something like this:

GET consultant/item/_search
{
  "from": 0,
  "size": 24,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "language_id": 1
          }
        },
        {
          "term": {
            "office.data.office.city_id": 1
          }
        }
      ]
    }
  }
}

This returns zero results, however, if I remove the second term and leave it only with the language_id clause, then it works as expected.

I'm sure this is down to a misunderstading on my part of how the nested object is flattened, but I'm out of ideas - I've tried all kinds of permutations of the query and mappings.

Any guidance hugely appreciated. I am using Elasticsearch 6.1.1.

To search a field of a nested object, you must wrap the query inside a nested query. — Chin Huang
– Chin Huang, Commented Jan 10, 2018 at 15:17
Thanks @ChinHuang, I read up on this, and combined with MrSimple's answer found, and understood, the solution! — HelloPablo
– HelloPablo, Commented Jan 10, 2018 at 17:20

MrSimple · Accepted Answer · 2018-01-10 16:01:34Z

I'm not sure if you need the entire record or not, this solution gives every record that has language_id: 1 and has an office.data.office.id: 1 value.

GET consultant/item/_search
{
  "from": 0,
  "size": 100,
  "query": {
    "bool":{
      "must": [
        {
          "term": {
            "language_id": {
              "value": 1
            }
          }
        },
        {
          "nested": {
            "path": "office.data",
            "query": {
              "match": {
                      "office.data.office.city_id": 1
              }
            }
          }
        }
      ]
    }
  }
}

I put 3 different records in my test index for proofing against false hits, one with different language_id and one with different office ids and only the matching one returned.
If you only need the office data, then that's a bit different but still solvable.

Collectives™ on Stack Overflow

Elasticsearch query fails to return results when querying a nested object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related