CODEWITHSUNDEEP
elasticsearchjoin
000000000000000000000
000000000000000000000

Reputation: 880

Elasticsearch find the total number of grandchildren

I am trying to set up a mapping using JOIN in Elasticsearch for my data, which have the relationship: company is in an one-to-many relationship with group, and group is in an one-to-many relationship with user.

Here is my mapping:

PUT /company
{
  "settings": {
    "number_of_shards": 1, "number_of_replicas": 0,
    "mapping.single_type": true
  },
  "mappings": {
    "doc": {
      "properties": {
        "company": { "type": "text" },
        "group": { "type": "text" },
        "user": { "type": "text" },
        "company_relations": {
          "type": "join",
          "relations": {
            "company": "group",
            "group": "user"
          }
        }
      }
    }
  }
}

After I have created the mapping and populated the index. How do I query for how many users does a specific company have?

Upvotes: 0

Views: 49

Answers (2)

Kamal Kunjapur
Kamal Kunjapur

Reputation: 8840

First of all, change your mapping to the below. The reason is you cannot apply and perform aggregation queries on the text field. You would need keyword field for that.

Also I would not advise you to enable fielddata: true as it may have impact on the performance once your index size increases plus it makes no sense. More on this here

{ 
   "settings":{ 
      "number_of_shards":1,
      "number_of_replicas":0,
      "mapping.single_type":true
   },
   "mappings":{ 
      "doc":{ 
         "properties":{ 
            "company":{ 
               "type":"text",
               "fields":{ 
                  "keyword":{ 
                     "type":"keyword"
                  }
               }
            },
            "group":{ 
               "type":"text"
            },
            "user":{ 
               "type":"text",
               "fields":{ 
                  "keyword":{ 
                     "type":"keyword"
                  }
               }
            },
            "company_relations":{ 
               "type":"join",
               "relations":{ 
                  "company":"group",
                  "group":"user"
               }
            }
         }
      }
   }
}

Post that ingest your document as below (For the sake of simplicity, I've just used concerned fields and not made use of relations)

Sample Docs:

POST company/doc/1
{
  "company": "daimler",
  "group": "abc",
  "user": "austin"
}

POST company/doc/2
{
  "company": "daimler",
  "group": "abc",
  "user": "matt"
}

Now your query could be as simple as below where I've used Terms Aggregation and Cardinality Aggregation to get what you are looking for:

Aggregation Query:

POST company/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "company.keyword": "daimler"
          }
        }
      ]
    }
  }, 
  "aggs": {
    "my_companies": {
      "terms": {
        "field": "company.keyword",
        "size": 10
      },
      "aggs": {
        "my_users_count": {
          "cardinality": {
            "field": "user.keyword"
          }
        }
      }
    }
  }
}

Note that I've used aggregation on company.keyword and user.keyword fields.

Below is how response appears

Response:

{
  "took" : 7,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "my_companies" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "daimler",
          "doc_count" : 2,
          "my_users_count" : {
            "value" : 2
          }
        }
      ]
    }
  }
}

Hope that helps!

Upvotes: 1

Assael Azran
Assael Azran

Reputation: 2993

Change your mapping to:

PUT /company
{

  "mappings": {
    "doc": {
      "properties": {
        "company": { "type": "text"},
        "group": { "type": "text" },
        "user": { "type": "text","fielddata": true},
        "company_relations": {
          "type": "join",
          "relations": {
            "company": "group",
            "group": "user"
          }
        }
      }
    }
  }
}

Then use this query:

GET company/_search
{
  "size": 0, 
  "query": {
    "match": {
      "company": "company name"
    }
  },"aggs": {
    "total_users": {
      "value_count": {
         "field": "user"
      }
    }
  }
}

Upvotes: 1

Related Questions