1

I have nested objects in my elastic search index of the following type

 "_source": {
           "NAME": "MNQ",
           "LAST_MOD_DATE": 1373587200000,
           "ACTIVE_FL": "Y",
           "ID": "1008",
           "USER": [
              {
                 "USR_ID": 499,
                 "STATUS": "INACTV",
                 "NAME": "ABC"
              },
              {
                 "USR_ID": 53,
                 "STATUS": "ACTV",
                 "NAME": "XYZ"
              }
            ]
        }

And I have following use cases for querying the index:

  • Get all active users for a particular id. Eg: I want to get users that are active for id 1008 which in this case would be user XYZ
  • Get all active users. Eg: I perform a match_all query and I wan to aggregate on term USER.NAME but it should only return me the names of active users.

I am having trouble performing these nested operations as search for active status will return a record which has even one of the users as active. I am unable to specifically filter out inactive users. Any help in this regard is greatly appreciated.

Improve this question

1 Answer 1

Reset to default
0

since you are always interested in fetching the users then in that case parent-child relationship will do better than nested documents type for users. As with nested type the response will have unnecessary payload with inner_hits for elastic.

As the provide better navigation and more flexible queries for nested association then nested type.

Also in mappings you may have to select type to keyword or create a custom analyzer to retain the case as you are searching as case sensitive search.

Mappings for parent-child relationship

PUT parent_child_index
{
    "mappings": {
        "parent_document": {
            "properties": {
                "NAME": {
                    "type": "keyword"
                }
            }
        },
        "user": {
            "_parent": {
                "type": "parent_document"
            },
            "properties": {
                "USER_ID": {
                    "type": "keyword"
                },
                "STATUS": {
                    "type": "keyword"
                },
                "NAME": {
                    "type": "keyword"
                }
            }
        }
    }
}

Index parent child documents

POST parent_child_index/parent_document
{
    "NAME": "MNQ",
    "LAST_MOD_DATE": 1373587200000,
    "ACTIVE_FL": "Y",
    "ID": "1008"
}


POST parent_child_index/user?parent=AVyBzQXmp_hWdUR22wGr
{
    "USR_ID": 53,
    "STATUS": "ACTV",
    "NAME": "XYZ"
}

Query

POST parent_child_index/user/_search
{
    "query": {
        "bool": {
            "must": [{
                    "has_parent": {
                        "parent_type": "parent_document",
                        "query": {
                            "bool": {
                                "must": [{
                                    "term": {
                                        "ID": {
                                            "value": "1008"
                                        }
                                    }
                                }]
                            }
                        }
                    }
                },
                {
                    "term": {
                        "STATUS": {
                            "value": "ACTV"
                        }
                    }
                }
            ]
        }
    }
}

POST parent_child_index/user/_search
{
    "size": 0,
    "aggs": {
        "active_users": {
            "filter": {
                "term": {
                    "STATUS": "ACTV"
                }
            },
            "aggs": {
                "user_name": {
                    "terms": {
                        "field": "NAME",
                        "size": 10
                    }
                }
            }
        }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for a detailed response. It isn't easy to implement in my current setup and has raised another issue which I have detailed here: stackoverflow.com/questions/44423284/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.