# How to do sequence matching **URL:** \ **Category:** Elasticsearch\ **Created:** [August 21, 2014, 3:21am UTC](https://discuss.elastic.co/t/how-to-do-sequence-matching/19378 "2014-08-21T03:21:03Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Smitha\_Gowda](https://avatars.discourse-cdn.com/v4/letter/s/e47c2d/32.png) [@Smitha\_Gowda](https://discuss.elastic.co/u/Smitha_Gowda)\ **Post date:** [August 21, 2014, 3:21am UTC](https://discuss.elastic.co/t/how-to-do-sequence-matching/19378/1 "2014-08-21T03:21:03Z")
Hello Elastic Search Experts, I have recently started to look at Elastic Search to answer my analytics questions. I am fairly new to ES query, so please excuse me if this a obvious question I have session data with events like below JSON. The Number of events and order can vary in each document. I would like to GET all sessions with a specific sequence of events. For below example, Sequence of Event represented like "ABC". This document would be matched with any regular expression query like \*, _A_, _BC_, ABC.. . Non matching queries for the below data _D_, _AC_, AC.. How do I represent the Sequence property below to be indexed properly for the search? Or Am I thinking this data representation totally wrong? How do I build a query in ES for this? Any pointers will be helpful. Thanks. Session : { StartTime:"20130101T01:00" EndTime:"20130101T04:00" Sequence: Events: [ { Name: "A" StartTime:"20130101T01:00" EndTime:"20130101T02:00" }, { Name: "B" StartTime:"20130101T02:30" EndTime:"20130101T03:00" }, { Name: "C" StartTime:"20130101T03:30" EndTime:"20130101T04:00" } ] } -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/391e80ca-c774-4bf5-990a-39ac0b5e3c45%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/391e80ca-c774-4bf5-990a-39ac0b5e3c45%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![vineeth\_mohan\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vineeth_mohan_2/32/747_2.png) [@vineeth\_mohan\_2](https://discuss.elastic.co/u/vineeth_mohan_2)\ **Post date:** [August 21, 2014, 10:18am UTC](https://discuss.elastic.co/t/how-to-do-sequence-matching/19378/2 "2014-08-21T10:18:56Z")
Hello Smitha , You can try the wild card query - > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Thanks Vineeth On Thu, Aug 21, 2014 at 8:51 AM, Smitha Gowda [smithagowda@gmail.com](mailto:smithagowda@gmail.com) wrote: > Hello Elastic Search Experts, > > I have recently started to look at Elastic Search to answer my analytics > questions. I am fairly new to ES query, so please excuse me if this a > obvious question > > I have session data with events like below JSON. The Number of events and > order can vary in each document. I would like to GET all sessions with a > specific sequence of events. For below example, Sequence of Event > represented like "ABC". This document would be matched with any regular > expression query like \*, _A_, _BC_, ABC.. . Non matching queries for the > below data _D_, _AC_, AC.. > How do I represent the Sequence property below to be indexed properly for > the search? Or Am I thinking this data representation totally wrong? > How do I build a query in ES for this? > > Any pointers will be helpful. Thanks. > > Session : > { > StartTime:"20130101T01:00" > EndTime:"20130101T04:00" > Sequence: > Events: [ > { > Name: "A" > StartTime:"20130101T01:00" > EndTime:"20130101T02:00" > }, > { > Name: "B" > StartTime:"20130101T02:30" > EndTime:"20130101T03:00" > }, > { > Name: "C" > StartTime:"20130101T03:30" > EndTime:"20130101T04:00" > } > ] > } > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/391e80ca-c774-4bf5-990a-39ac0b5e3c45%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/391e80ca-c774-4bf5-990a-39ac0b5e3c45%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/391e80ca-c774-4bf5-990a-39ac0b5e3c45%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/391e80ca-c774-4bf5-990a-39ac0b5e3c45%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kXqRECS224-m7UCLQb3yHzx0Pwt41cwyj%2B-0MRNNbo5A%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kXqRECS224-m7UCLQb3yHzx0Pwt41cwyj%2B-0MRNNbo5A%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Smitha\_Gowda](https://avatars.discourse-cdn.com/v4/letter/s/e47c2d/32.png) [@Smitha\_Gowda](https://discuss.elastic.co/u/Smitha_Gowda)\ **Post date:** [August 22, 2014, 2:46am UTC](https://discuss.elastic.co/t/how-to-do-sequence-matching/19378/3 "2014-08-22T02:46:44Z")
Thanks that will work. One more question related to Kibana to visualize this data. For a query that matches sequence "AB" Once I have all the matching documents I want to plot a bar chart with x-axis: Session StartTime (Day granularity) y-axis: Mean of (LastEvent.EndTime(In this example B) - FirstEvent.StartTime(In this Example A)) for the given day Any pointers on how do I aggregate on other properties on the matched document? Thanks in advance! -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![vineeth\_mohan\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vineeth_mohan_2/32/747_2.png) [@vineeth\_mohan\_2](https://discuss.elastic.co/u/vineeth_mohan_2)\ **Post date:** [August 22, 2014, 9:26am UTC](https://discuss.elastic.co/t/how-to-do-sequence-matching/19378/4 "2014-08-22T09:26:13Z")
Hello Smitha , Please be more elaborate. What is the sequence AB , what is event here and what is last and first event. Thanks Vineeth On Fri, Aug 22, 2014 at 8:16 AM, Smitha Gowda [smithagowda@gmail.com](mailto:smithagowda@gmail.com) wrote: > Thanks that will work. > > One more question related to Kibana to visualize this data. > > For a query that matches sequence "AB" > Once I have all the matching documents I want to plot a bar chart with > x-axis: Session StartTime (Day granularity) > y-axis: Mean of (LastEvent.EndTime(In this example B) - > FirstEvent.StartTime(In this Example A)) for the given day > > Any pointers on how do I aggregate on other properties on the matched > document? > > Thanks in advance! > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5%3DMedq5ta5G%2BvX4Hzi19PyY6s9Kc2JFL-2mrkca\_mvbww%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5%3DMedq5ta5G%2BvX4Hzi19PyY6s9Kc2JFL-2mrkca_mvbww%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Smitha\_Gowda](https://avatars.discourse-cdn.com/v4/letter/s/e47c2d/32.png) [@Smitha\_Gowda](https://discuss.elastic.co/u/Smitha_Gowda)\ **Post date:** [August 23, 2014, 1:32am UTC](https://discuss.elastic.co/t/how-to-do-sequence-matching/19378/5 "2014-08-23T01:32:27Z")
Sure. Going back to my original example This is a session document containing a sequence of events occurring in specific interval Session : { StartTime:"20130101T01:00" EndTime:"20130101T04:00" Sequence: "A B C" Events: [ { Name: "A" StartTime:"20130101T01:00" EndTime:"20130101T02:00" }, { Name: "B" StartTime:"20130101T02:30" EndTime:"20130101T03:00" }, { Name: "C" StartTime:"20130101T03:30" EndTime:"20130101T04:00" } ] } What I want 1. Match all the documents having a specific sequence of events, say "B C" 2. On the result, bucket aggregate documents by day on Session.StartTime (Date\_Histogram) 3. on each bucket find the average of time elapsed in seconds between the searched sequence. Here it was "B, C", so it will be session.Events[indexOfC].EndTime - session.Events[indexOfB].StartTime I tried bucket filter aggregation on #1, seems to be working I tried date\_histogram for #2, not working,\* I am not sure how to consume the result of #1 in #2\* _I have not reached to trying #3 because #2 is not working, but I think I need avg aggr with script value._ Can you help with syntax or pointer on highlighted. _I am also interested in how I feed it to a Kibana chart._ Thanks in advance. On Fri, Aug 22, 2014 at 2:26 AM, vineeth mohan [vm.vineethmohan@gmail.com](mailto:vm.vineethmohan@gmail.com) wrote: > Hello Smitha , > > Please be more elaborate. > What is the sequence AB , what is event here and what is last and first > event. > > Thanks > Vineeth > > On Fri, Aug 22, 2014 at 8:16 AM, Smitha Gowda [smithagowda@gmail.com](mailto:smithagowda@gmail.com) > wrote: > > > Thanks that will work. > > > > One more question related to Kibana to visualize this data. > > > > For a query that matches sequence "AB" > > Once I have all the matching documents I want to plot a bar chart with > > x-axis: Session StartTime (Day granularity) > > y-axis: Mean of (LastEvent.EndTime(In this example B) - > > FirstEvent.StartTime(In this Example A)) for the given day > > > > Any pointers on how do I aggregate on other properties on the matched > > document? > > > > Thanks in advance! > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/320375d3-a0f6-402e-92a0-08279b1f7c7c%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/0DT9B499joU/unsubscribe](https://groups.google.com/d/topic/elasticsearch/0DT9B499joU/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5%3DMedq5ta5G%2BvX4Hzi19PyY6s9Kc2JFL-2mrkca\_mvbww%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5%3DMedq5ta5G%2BvX4Hzi19PyY6s9Kc2JFL-2mrkca_mvbww%40mail.gmail.com) > [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5%3DMedq5ta5G%2BvX4Hzi19PyY6s9Kc2JFL-2mrkca\_mvbww%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5%3DMedq5ta5G%2BvX4Hzi19PyY6s9Kc2JFL-2mrkca_mvbww%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CACyOFkRDH\_6B\_r0XJ0st%3Dx0wS7Z%3DOsdqgGHghqAsO-N%3DFvSS1Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CACyOFkRDH_6B_r0XJ0st%3Dx0wS7Z%3DOsdqgGHghqAsO-N%3DFvSS1Q%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:06am UTC](https://discuss.elastic.co/t/how-to-do-sequence-matching/19378/6 "2017-07-06T01:06:51Z")