Has_child issue with filtered query and aggs

Elastic Stack Elasticsearch
1

I am a beginner. There is a such a thing as too much information, so I will try to be specific.

I have 2 tables/types in an index. table 1 is parent. table 2 is child mapped to parent.
I am basically trying to convert an SQL join with aggregations. Maybe 2 queries is better?
In any case here is my attempt to query the parent/child.
POST /my_index/table1/_search
{
"has_child":{
"type": "table2",
"fields": ["name","color","acc_id","time_hr"],
"filtered" : {
"query" : { "match": { "acc_id" : 60}},
"filter" : {
"bool":{
"must":{"range":{"time_hr" : { "gte": 1417300000, "lt": 1418000000}}},
}
},
"aggs":{
"count":{ "sum": {"field": "count"}},
"total_time":{ "sum": {"field": "cost"}}
}
}
}
}
Here's the error:
"error": {
"root_cause": [
{
"type": "search_parse_exception",
"reason": "failed to parse search source. unknown search element [has_child]",

Hopefully a simple answer to get me moving. I tried to do my research. Much to learn.

2

This post can be deleted.

3

Heya,

You miss a "query" element around your has_child query. Like so:

POST /my_index/table1/_search
{
  "query": {
    "has_child": {
      "type": "table2",
      "fields": [
        "name",
        "color",
        "acc_id",
        "time_hr"
      ],
      "filtered": {
        "query": {
          "match": {
            "acc_id": 60
          }
        },
        "filter": {
          "bool": {
            "must": {
              "range": {
                "time_hr": {
                  "gte": 1417300000,
                  "lt": 1418000000
                }
              }
            }
          }
        }
      },
      "aggs": {
        "count": {
          "sum": {
            "field": "count"
          }
        },
        "total_time": {
          "sum": {
            "field": "cost"
          }
        }
      }
    }
  }
}

In general I can recommend using Sense - it's autocomplete features greatly help in getting the structure right. See https://www.elastic.co/guide/en/sense/current/introduction.html

4

Thank You very much.

I, however ended up denormalizing the data between the 2 tables.

I will try Sense!