CODEWITHSUNDEEP
elasticsearch
Sean Montana
Sean Montana

Reputation: 445

Elasticsearch Filtering Parents by Filtered Child Document Count

I'm attempting to do some elasticsearch query fu on a set of data I have. I have a user document that is the parent to many child page view documents. I'm looking to return all users that have viewed a specific page an arbitrary amount of times (defined by user input box). So far, I've got a has_child query that will return me all the users that have a page view with certain ids. However, this will return those parents with all their children. Next, I've tried to write an aggregation on those query results, that will essentially do the same has_child query in aggregation form. Now, I have the right document count for my filtered child documents. I need to use this document count to go back and filter the parents. To explain the query in words, "return to me all the users that have viewed a specific page more than 4 times". It's possible that I may need to restructure my data. Any thoughts?

Here is my query thus far:

curl -XGET 'http://localhost:9200/development_users/_search?pretty=true' -d '
{
    "query" : { 
      "has_child" : {
        "type" : "page_view",
        "query" : {
          "terms" : {
            "viewed_id" : [175,180]
          }
        }
      }
    },
    "aggs" : {
      "to_page_view": {
        "children": {
          "type" : "page_view"
        },
        "aggs" : {
          "page_views_that_match" : {
            "filter" : { "terms": { "viewed_id" : [175,180] } }
          }
        }
      }
    }
}'

This returns me a response like:

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [ {
      "_index" : "development_users",
      "_type" : "user",
      "_id" : "22548",
      "_score" : 1.0,
      "_source":{"id":22548,"account_id":1009}
    } ]
  },
  "aggregations" : {
    "to_page_view" : {
      "doc_count" : 53,
      "page_views_that_match" : {
        "doc_count" : 2
      }
    }
  }
}

Associated Mappings:

{
  "development_users" : {
    "mappings" : {
      "page_view" : {
        "dynamic" : "false",
        "_parent" : {
          "type" : "user"
        },
        "_routing" : {
          "required" : true
        },
        "properties" : {
          "created_at" : {
            "type" : "date",
            "format" : "date_time"
          },
          "id" : {
            "type" : "integer"
          },
          "viewed_id" : {
            "type" : "integer"
          },
          "time_on_page" : {
            "type" : "integer"
          },
          "title" : {
            "type" : "string"
          },
          "type" : {
            "type" : "string"
          },
          "updated_at" : {
            "type" : "date",
            "format" : "date_time"
          },
          "url" : {
            "type" : "string"
          }
        }
      },
      "user" : {
        "dynamic" : "false",
        "properties" : {
          "account_id" : {
            "type" : "integer"
          },
          "id" : {
            "type" : "integer"
          }
        }
      }
    }
  }
}

Upvotes: 1

Views: 2587

Answers (1)

Sloan Ahrens
Sloan Ahrens

Reputation: 8718

Okay, so this is kind of involved. I made a few simplifications to keep it straight in my head. First, I used this mapping:

PUT /test_index
{
    "mappings": {
        "page_view": {
            "_parent": {
               "type": "development_user"
            },
            "properties": {
                "viewed_id": {
                    "type": "string"
                }
            }
        },
        "development_user": {
            "properties": {
                "id": {
                    "type": "string"
                }
            }
        }
    }
}

Then I added some data. In this little universe, I have three users and two pages. I want to find users who have viewed "page_a" at least twice, so if I construct the correct query only user 3 will be returned.

POST /test_index/development_user/_bulk
{"index":{"_type":"development_user","_id":1}}
{"id":"user_1"}
{"index":{"_type":"page_view","_parent":1}}
{"viewed_id":"page_a"}
{"index":{"_type":"development_user","_id":2}}
{"id":"user_2"}
{"index":{"_type":"page_view","_parent":2}}
{"viewed_id":"page_b"}
{"index":{"_type":"development_user","_id":3}}
{"id":"user_3"}
{"index":{"_type":"page_view","_parent":3}}
{"viewed_id":"page_a"}
{"index":{"_type":"page_view","_parent":3}}
{"viewed_id":"page_a"}
{"index":{"_type":"page_view","_parent":3}}
{"viewed_id":"page_b"}

To get that answer we'll use aggregations. Notice that I don't want documents returned (the normal way), but I do want to filter down the documents we analyze, because it will make things more efficient. So I use the same basic filter you had before.

So the aggregation tree starts with terms_parent_id which will just separate parent documents. Inside that I have children_page_view which filters the child documents down to the ones I want ("page_a"), and next to it in the hierarchy is bucket_selector_page_id_term_count which uses a bucket selector (you'll need ES 2.x) to filter the parent documents by those meeting the criterium, and then finally a top hits aggregation which shows us the documents that match the requirements. 

POST /test_index/development_user/_search
{
   "size": 0,
   "query": {
      "has_child": {
         "type": "page_view",
         "query": {
            "terms": {
               "viewed_id": [
                  "page_a"
               ]
            }
         }
      }
   },
   "aggs": {
      "terms_parent_id": {
         "terms": {
            "field": "id"
         },
         "aggs": {
            "children_page_view": {
               "children": {
                  "type": "page_view"
               },
               "aggs": {
                  "filter_page_ids": {
                     "filter": {
                        "terms": {
                           "viewed_id": [
                              "page_a"
                           ]
                        }
                     }
                  }
               }
            },
            "bucket_selector_page_id_term_count": {
               "bucket_selector": {
                  "buckets_path": {
                     "children_count": "children_page_view>filter_page_ids._count"
                  },
                  "script": "children_count >= 2"
               }
            },
            "top_hits_users": {
               "top_hits": {
                  "_source": {
                     "include": [
                        "id"
                     ]
                  }
               }
            }
         }
      }
   }
}

which returns:

{
   "took": 14,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 2,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "terms_parent_id": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
            {
               "key": "user_3",
               "doc_count": 1,
               "children_page_view": {
                  "doc_count": 3,
                  "filter_page_ids": {
                     "doc_count": 2
                  }
               },
               "top_hits_users": {
                  "hits": {
                     "total": 1,
                     "max_score": 1,
                     "hits": [
                        {
                           "_index": "test_index",
                           "_type": "development_user",
                           "_id": "3",
                           "_score": 1,
                           "_source": {
                              "id": "user_3"
                           }
                        }
                     ]
                  }
               }
            }
         ]
      }
   }
}

Here's all the code I used:

http://sense.qbox.io/gist/43f24461448519dc884039db40ebd8e2f5b7304f

Upvotes: 7

Related Questions