# Best mapping/architecture for querying latest members from join/leave documents?

**URL:** <https://discuss.elastic.co/t/best-mapping-architecture-for-querying-latest-members-from-join-leave-documents/111695>\
**Category:** Elasticsearch\
**Created:** [December 14, 2017, 7:16am UTC](https://discuss.elastic.co/t/best-mapping-architecture-for-querying-latest-members-from-join-leave-documents/111695 "2017-12-14T07:16:34Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![michaelcheung](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michaelcheung/32/53285_2.png) [@michaelcheung](https://discuss.elastic.co/u/michaelcheung)\
**Post date:** [December 14, 2017, 7:16am UTC](https://discuss.elastic.co/t/best-mapping-architecture-for-querying-latest-members-from-join-leave-documents/111695/1 "2017-12-14T07:16:35Z")

</div>

There are a number of groups, where people can join and leave any groups. I have logs on all join and leave action, i.e. {"user": "Alice", "action": "JOIN", "group": "group-0", "@timestamp": "2017-12-12T20:00:00.000Z"} and {"user": "Alice", "action": "LEAVE", "group": "group-0", "@timestamp": "2017-12-13T20:00:00.000Z"}. People are allowed to join again after leave.

I want to query who is in `group-0` from these logs. I cannot make a visualization that "add person when join & delete when leave", or make a suitable query.

Any ways to get the list of current people in `group-0`? If no, what's more should I log or restructure to fulfil my needs?

Thanks,  
Michael

===== EDIT =====  
I come up with a solution, to have a "member list" document for each group, using script update to update the list. This is one POC logstash conf file.

```auto
input {
    stdin {
    }
}

filter {
    grok {
        match => { "message" => "%{NUMBER:updateTimestamp} %{NOTSPACE:action} %{NOTSPACE:group} %{NOTSPACE:user}" }
    }

    ruby {
        code => "
            event.set('memberList', Array.new);
            e = {};
            e['user'] = event.get('user');
            event.set('memberList', event.get('memberList') << e);
        "
    }

}

output {
    elasticsearch {
        hosts => ["..."]
        index => "group"
        document_type => "members"
        document_id => "%{group}"
        manage_template => false
        action => "update"
        doc_as_upsert => true
        script_lang => "painless"
        script_type => "inline"
        script => '
            if (ctx._source.updateTimestamp != params.event.get("updateTimestamp")) {
                ctx._source.updateTimestamp = params.event.get("updateTimestamp");
                if (params.event.get("action") == "JOIN") {
                    ctx._source.memberList.add(params.event.get("memberList")[0]);
                } else if (params.event.get("action") == "LEAVE") {
                    def list = ctx._source.memberList;
                    list.remove(list.indexOf(params.event.get("memberList")[0]));
                }
            }
        '
    }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 11, 2018, 7:16am UTC](https://discuss.elastic.co/t/best-mapping-architecture-for-querying-latest-members-from-join-leave-documents/111695/2 "2018-01-11T07:16:48Z")

</div>

Closed on January 11, 2018, 7:16am UTC

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.