Join of reverse_nested aggregations


(Serg) #1

Hi, there!

Please help me to find a mechanism to aggregate over the following domain or to prove that it doesn't exist in the current API.

curl -XDELETE 127.0.0.1:9200/test_index

    curl -XPUT 127.0.0.1:9200/test_index -d '{
        "mappings": {
            "contact": {
                "properties": {
                    "facebook_profile": {
                        "type": "nested",
                        "properties": {
                            "education": {
                                "type": "string"
                            },
                            "year": {
                                "type": "integer"
                            }
                        }
                    },
                    "google_profile": {
                        "type": "nested",
                        "properties": {
                            "education": {
                                "type": "string"
                            },
                            "year": {
                                "type": "integer"
                            }
                        }
                    }
                }
            }
        }
    }'
    
    curl -XPUT 127.0.0.1:9200/test_index/contact/contact1 -d '{
        "google_profile": {
            "education": "stanford", "year": 1990
        }
    }'
    
    curl -XPUT 127.0.0.1:9200/test_index/contact/contact2 -d '
    {
        "facebook_profile": {
            "education": "stanford", "year": 1990
        }
    }'

How one can query ES to find statistics about how many of contacts graduated from particular universities?

I found one possibility, but it doesn't give me desired result, since it can't answer on the question above with respect to contacts, but only to their particular profiles (nested docs):

    curl -XPOST '127.0.0.1:9200/test_index/_search?search_type=count&pretty=true' -d '{
        "aggs": {
            "facebook_educations": {
                "aggs": {
                    "field": {
                        "terms": {
                            "field": "contact.facebook_profile.education"
                        },
                        "aggs": {
                            "reverse": {
                                "reverse_nested": {
                                }
                            }
                        }
                    }
                },
                "nested": {
                    "path": "contact.facebook_profile"
                }
            },
            "google_educations": {
                "aggs": {
                    "field": {
                        "terms": {
                            "field": "contact.google_profile.education"
                        },
                        "aggs": {
                            "reverse": {
                                "reverse_nested": {
                                }
                            }
                        }
                    }
                },
                "nested": {
                    "path": "contact.google_profile"
                }
            }
        }
    }'

What gives me:

    "aggregations" : {
        "facebook_educations" : {
          "doc_count" : 1,
          "field" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ {
              "key" : "stanford",
              "doc_count" : 1,
              "reverse" : {
                "doc_count" : 1
              }
            } ]
          }
        },
        "google_educations" : {
          "doc_count" : 1,
          "field" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ {
              "key" : "stanford",
              "doc_count" : 1,
              "reverse" : {
                "doc_count" : 1
              }
            } ]
          }
        }
    }

But here I can't be sure if one found contact is the same or different doc(parent), respectively I can't answer to my initial question.

Thank you for any advice.


(system) #2