ElasticSearch query doesn't return documents that have an "empty" nested property

Question

I'm running into a weird problem. I have a document mapping for which one of the properties is a nested object.

{
    "userLog": {
        "properties": {
            "userInfo": {
                "userId": {
                    "type": "text"
                },
                "firstName": {
                    "type": "text"
                },
                "lastName": {
                    "type": "text"
                },
                "email": {
                    "type": "text"
                }
            },
            "violations": {
                "type": "integer"
            },
            "malfunctions": {
                "type": "integer"
            },
            "extensionsUsed": {
                "type": "integer"
            },
            "date": {
                "type": "date",
                "format": "yyyy-MM-dd||yyyy/MM/dd||yyyyMMdd||epoch_millis"
            },
            "events": {
                "type": "nested",
                "properties": {
                    "editorId": {
                        "type": "text"
                    },
                    "editorRole": {
                        "type": "text"
                    },
                    "editedTimestamp": {
                        "type": "date",
                        "format": "epoch_millis"
                    },
                    "createdTimestamp": {
                        "type": "date",
                        "format": "epoch_millis"
                    },
                    "userId": {
                        "type": "text"
                    },
                    "timestamp": {
                        "type": "date",
                        "format": "epoch_millis"
                    },
                    "eventType": {
                        "type": "text"
                    }
                }
            }
        }
    }
}

Some userLogs have events and some don't. My queries only return userLogs that have events, however, and I'm not sure why. There are definitely userLogs that exist without events in the index. I can see them in Kibana. They just aren't returned in the search. Here's what I'm running for a query:

GET index_name/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "date": {
              "gte": "20170913",
              "format": "yyyyMMdd"
            }
          }
        }
      ],
      "should": [
        {
          "match_phrase": {
            "userInfo.userId": "Xvo9qblajOVaM3bQQMaV4GKk7S42"
          }
        }
      ],
      "minimum_number_should_match": 1
    }
  }
}

based on this discussion

I modified my query to be the following:

GET one20_eld_portal/_search
{
    "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "events",
            "query": {
              "bool": {
                "filter": {
                  "exists": {
                    "field": "events.userId"
                  }
                }
              }
            }
          }
        }
      ],
        "should": [
          {
            "match_phrase": {
              "userInfo.uid": "Xvo9qblajOVaM3bQQMaV4GKk7S42"
            }
          }
        ],
        "minimum_should_match": 1
      }
  }
}

but this doesn't return any results. Any help is greatly appreciated!

You have posted two queries. As of what I gathered, first query is running and second is not? Correct me if I am wrong.. — Richa
@Richa I was just showing what I've tried so far. The second query doesn't return any results but the first only returns results with non-empty events — beardo34
One More question.. What is the criteria a document should fulfill ? According to first query date should be greater than a given value AND userInfo.userId should be Xvo9qblajOVaM3bQQMaV4GKk7S42. While second query says events.userId MUST NOT exist and userInfo.userId should be Xvo9qblajOVaM3bQQMaV4GKk7S42. Both are different . Can you explain in simple terms . We will try to convert that into Elastic query — Richa
@Richa I found the issue, it turns out the mechanism for creating the userLog wasn't setting the userInfo.userId properly. Huge oversight on my part, thanks for your help! — beardo34

beardo34 beardo34 · Accepted Answer · 2017-09-15T17:43:01

Turns out the reason the "empty" logs weren't being returned is because the userId wasn't being set properly for empty logs.

ElasticSearch query doesn't return documents that have an "empty" nested property

1 Answers