Nested facets filtering by parent in ElasticSearch

es_nested_facets_filter.sh

#!/bin/bash
# ========================================
# Nested facets filtering by parent in ElasticSearch
# --------------------------------------------------
# I need to get facets of departments for specific person for one movie genre:
# facets for credit.department where genre=comedy, credits.person_id=1
# I am using include_in_parent:true mapping to be able to facet_filter by parent genre
# ========================================

curl -X DELETE localhost:9200/movies
curl -X PUT localhost:9200/movies -d '
{
  "mappings": {
    "movie": {
      "properties": {
        "name": { "type": "string" },
        "genre":  { "type": "string", "index": "not_analyzed" },
        "credits": {
          "type": "nested",
          "include_in_parent": true,
          "properties": {
            "person_id": { "type": "integer" },
            "department": { "type": "string", "index": "not_analyzed" }
          }
        }
      }
    }
  }
}'


curl -X PUT localhost:9200/movies/movie/1 -d '
{
  "name": "Movie A",
  "genre": "comedy",
  "credits": [
    {
      "person_id": 1,
      "department": "director"
    },
    {
      "person_id": 2,
      "department": "actor"
    },
    {
      "person_id": 3,
      "department": "producer"
    }
  ]
}'

curl -X PUT localhost:9200/movies/movie/2 -d '
{
  "name": "Movie B",
  "genre": "horror",
  "credits": [
    {
      "person_id": 1,
      "department": "director"
    },
    {
      "person_id": 2,
      "department": "actor"
    },
    {
      "person_id": 3,
      "department": "producer"
    }
  ]
}'

curl -X POST "http://localhost:9200/movies/_refresh"

curl -X POST "http://localhost:9200/movies/_search?pretty=true" -d '
{
  "query": {
    "nested": {
      "path": "credits",
      "query": {
        "match": {
           "credits.person_id": 1
        }
      }
    }
  },
  "facets": {
    "comedy_departments": {
      "terms": {
        "field": "credits.department"
      },
      "facet_filter": {
        "and": [
          {
            "term": {
              "genre": "comedy"
            }
          },
          {
            "term": {
              "credits.person_id": 1
            }
          }
        ]
      }
    }
  },
  "size": 0
}'
echo
echo "# Returns facets for all persons, not where person_id=1"; echo
# Result:
# "facets" : {
#  "comedy_departments" : {
#    "_type" : "terms",
#    "missing" : 0,
#    "total" : 3,
#    "other" : 0,
#    "terms" : [ {
#      "term" : "producer",
#      "count" : 1
#    }, {
#      "term" : "director",
#      "count" : 1
#    }, {
#      "term" : "actor",
#      "count" : 1
#    } ]
#  }
#}

curl -X POST "http://localhost:9200/movies/_search?pretty=true" -d '
{
  "query": {
    "term": {
       "credits.person_id": 1
    }
  },
  "facets": {
    "comedy_departments": {
      "terms": {
        "field": "credits.department"
      },
      "facet_filter": {
        "and": [
          {
            "term": {
              "genre": "comedy"
            }
          },
          {
            "nested": {
              "path": "credits",
                "query": {
                  "match": {
                    "credits.person_id": 1
                  }
                }
              }
          }
        ]
      }
    }
  },
  "size": 0
}'
echo
echo "# Trying using nested filtering. Returns facets for all persons, not where person_id=1"; echo
# Result:
# "facets" : {
#  "comedy_departments" : {
#    "_type" : "terms",
#    "missing" : 0,
#    "total" : 3,
#    "other" : 0,
#    "terms" : [ {
#      "term" : "producer",
#      "count" : 1
#    }, {
#      "term" : "director",
#      "count" : 1
#    }, {
#      "term" : "actor",
#      "count" : 1
#    } ]
#  }
#}

curl -X POST "http://localhost:9200/movies/_search?pretty=true" -d '
{
   "query": {
      "term": {
         "credits.person_id": 1
      }
   },
   "facets": {
      "comedy_departments": {
         "terms": {
            "field": "credits.department"
         },
         "nested": "credits",
         "facet_filter": {
            "and": [
               {
                  "nested": {
                     "path": "credits",
                     "query": {
                        "match": {
                           "credits.person_id": 1
                        }
                     },
                     "join": false
                  }
               }
            ]
         }
      }
   },
   "size": 0
}'
echo
echo "# Now results all correct, but i removed genre=comedy by parent field, which i need"; echo
# Result:
# "facets" : {
#  "comedy_departments" : {
#    "_type" : "terms",
#    "missing" : 0,
#    "total" : 2,
#    "other" : 0,
#    "terms" : [ {
#      "term" : "director",
#      "count" : 2
#    } ]
#  }
#}

curl -X POST "http://localhost:9200/movies/_search?pretty=true" -d '
{
  "query": {
    "term": {
       "credits.person_id": 1
    }
  },
  "facets": {
    "comedy_director_count": {
      "filter": {
        "and": [
          {
            "term": {
              "genre": "comedy"
            }
          },
          {
            "nested": {
              "path": "credits",
              "filter": {
                "and": [
                  {
                    "term": {
                       "credits.department": "director"
                    }
                  },
                  {
                    "term": {
                       "credits.person_id": 1
                    }
                  }
                ]
              }
            }
          }
        ]
      }
    }
  },
  "size": 0
}'
echo
echo "# When using filter facet count instead of facet_filter, count returned is correct.";
echo "# But counting for each department separately would be wrong :/"
echo
# Result:
# "facets" : {
#  "comedy_director_count" : {
#    "_type" : "filter",
#    "count" : 1
#  }
#}