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, Sep 15 '17 at 01:58
@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, Sep 15 '17 at 13:21
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, Sep 15 '17 at 15:39
@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, Sep 15 '17 at 17:40

score 0 · Accepted Answer · answered Sep 15 '17 at 17:43

0

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

answered Sep 15 '17 at 17:43

beardo34

58
1
1
7

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

1 Answers1