Is there any solution with elasticsearch parent-child join

Question

I have an es settings like following:

PUT /test
        {
        "mappings": {
         "doc": {
          "properties": {
            "status": {
              "type": "keyword"
            },
            "counting": {
              "type": "integer"
            },
            "join": {
              "type": "join",
              "relations": {
                "vsim": ["pool", "package"]
              }
            },
            "poolId": {
              "type": "keyword"
            },
            "packageId": {
              "type": "keyword"
            },
            "countries": {
              "type": "keyword"
            },
            "vId": {
              "type": "keyword"
            }
          }
        }
    }}

Then add data:

// add vsim
PUT /test/doc/doc1
{"counting":6, "join": {"name": "vsim"}, "content": "1", "status": "disabled"}

PUT /test/doc/doc2
{"counting":5,"join": {"name": "vsim"}, "content": "2", "status": "disabled"}

PUT /test/doc/doc3
{"counting":5,"join": {"name": "vsim"}, "content": "2", "status": "enabled"}

// add package
PUT /test/doc/ner2?routing=doc2
{"join": {"name": "package", "parent": "doc2"}, "countries":["CN", "UK"]}

PUT test/doc/ner12?routing=doc1
{"join": {"name": "package", "parent": "doc1"}, "countries":["CN", "US"]}

PUT /test/doc/ner11?routing=doc1
{"join":{"name": "package", "parent": "doc1"}, "countries":["US", "KR"]}

PUT /test/doc/ner13?routing=doc3
{"join":{"name": "package", "parent": "doc3"}, "countries":["UK", "AU"]}


// add pool
PUT /test/doc/ner21?routing=doc1
{"join": {"name": "pool", "parent": "doc1"}, "poolId": "MER"}

PUT /test/doc/ner22?routing=doc2
{"join": {"name": "pool", "parent": "doc2"}, "poolId": "MER"}

PUT /test/doc/ner23?routing=doc2
{"join": {"name": "pool", "parent": "doc2"}, "poolId": "NER"}

and then I want to count the counting group by the status(vsim), poolId(pool) and countries(package), the expect result like:

disabled-MER-CN: 3

disabled-MER-US: 3

enabled-MR-CN: 1 ... and so on. I'm a new player for elasticsearch, and I have learnt the document like

https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html and https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-children-aggregation.html

but still have no idea to implement this aggregation query, PLEASE give me some suggestion, thanks!

Mysterion Mysterion · Accepted Answer · 2019-01-03T13:29:44

If I followed your structure of the documents - you have types pool and package on the same level (they are siblings) - I wasn't able to achieve exactly your expected results. I also highly doubt that it's possible with those types being siblings.

However, it's still possible to slice per one field in your doc (status) and later separately slice both by poolId and countries with a query like this:

{
  "aggs": {
    "status-aggs": {
      "terms": {
        "field": "status",
        "size": 10
      },
      "aggs": {
        "to-pool": {
          "children": {
            "type": "pool"
          },
          "aggs": {
            "top-poolid": {
              "terms": {
                "field": "poolId",
                "size": 10
              }
            }
          }
        },
        "to-package": {
          "children": {
            "type": "package"
          },
          "aggs": {
            "top-countries": {
              "terms": {
                "field": "countries",
                "size": 10
              }
            }
          }
        }
      }
    }
  }
}

with a response from Elasticsearch like this (I've omitted some part of json for readability):

{
  "status-aggs": {
    "buckets": [
      {
        "key": "disabled",
        "doc_count": 2,
        "to-pool": {
          "doc_count": 3,
          "top-poolid": {
            "buckets": [
              {
                "key": "MER",
                "doc_count": 2
              },
              {
                "key": "NER",
                "doc_count": 1
              }
            ]
          }
        },
        "to-package": {
          "doc_count": 3,
          "top-countries": {
            "buckets": [
              {
                "key": "CN",
                "doc_count": 2
              },
              {
                "key": "US",
                "doc_count": 2
              },
              {
                "key": "KR",
                "doc_count": 1
              },
              {
                "key": "UK",
                "doc_count": 1
              }
            ]
          }
        }
      },
      {
        "key": "enabled",
        "doc_count": 1,
        "to-pool": {
          "doc_count": 0,
          "top-poolid": {
            "buckets": []
          }
        },
        "to-package": {
          "doc_count": 1,
          "top-countries": {
            "buckets": [
              {
                "key": "AU",
                "doc_count": 1
              },
              {
                "key": "UK",
                "doc_count": 1
              }
            ]
          }
        }
      }
    ]
  }
}

Is there any solution with elasticsearch parent-child join

1 Answers