Group and count by array of objects' keys

Question

Given the following index definition and query:

curl -XDELETE "localhost:9200/products"
curl -XPUT "localhost:9200/products"
curl -XPUT "localhost:9200/products/_mapping" -H 'Content-Type: application/json' -d'
{
  "properties": {
    "opinions": {
      "type": "nested",
      "properties": {
        "topic": {"type": "keyword"},
        "count": {"type": "long"}
      },
      "include_in_parent": true
    }
  }
}'

curl -X POST "localhost:9200/products/_bulk" -H 'Content-Type: application/json' -d'
{"index":{"_id":1}}
{"opinions":[{"topic": "room", "count": 2}, {"topic": "kitchen", "count": 1}]}
{"index":{"_id":2}}
{"opinions":[{"topic": "room", "count": 1}, {"topic": "restroom", "count": 1}]}
'

sleep 2
curl -X POST "localhost:9200/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "size": 0,
  "aggs": {
    "per_topic": {
      "terms": {"field": "opinions.topic"},
      "aggs": {
        "counts": {
          "sum": {"field": "opinions.count"}
        }
      }
    }
  }
}
'

Produces the result:

  "aggregations" : {
    "per_topic" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "room",
          "doc_count" : 2,
          "counts" : {
            "value" : 5.0
          }
        },
        {
          "key" : "kitchen",
          "doc_count" : 1,
          "counts" : {
            "value" : 3.0
          }
        },
        {
          "key" : "restroom",
          "doc_count" : 1,
          "counts" : {
            "value" : 2.0
          }
        }
      ]
    }
  }
}

I'm expecting the sum of room to be 3, kitchen to be 1 and restroom to be 1, counting only the related nested documents, but instead it is summing all the nested count fields in all the matched the documents.

How can I sum only the matched aggregated nested documents?

UPDATE: solution based on comments

curl -X POST "localhost:9200/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "size": 0,
  "aggs": {
    "opinions": {
      "nested": {"path": "opinions"},
      "aggs": {
        "per_topic": {
          "terms": {"field": "opinions.topic"},
          "aggs": {
            "counts": {
              "sum": {"field": "opinions.count"}
            }
          }
        }
      }
    }
  }
}
'

Are you sure that you are dealing with nested fields and not objects? — dcolazin
– dcolazin, Commented Feb 3, 2022 at 20:58
how to change to nested? would it make a difference in this query? — brauliobo
– brauliobo, Commented Feb 3, 2022 at 21:06
you can't change from non-nested object to nested inside a index, so you have to create a new one; maybe use a template to ensure the right type elastic.co/guide/en/elasticsearch/reference/current/nested.html see the example: it seems exactly this case — dcolazin
– dcolazin, Commented Feb 3, 2022 at 21:22
just recreated the index having opinions as nested and got the error Found 1 problem\nline 1:67: Grouping isn't (yet) compatible with nested fields [opinions.topic] — brauliobo
– brauliobo, Commented Feb 3, 2022 at 23:47
it is probably an undocumented SQL limitation elastic.co/guide/en/elasticsearch/reference/7.16/… anyway now you can write a suitable query in native elastic language; it is impossible to do so with non-nested objects — dcolazin
– dcolazin, Commented Feb 3, 2022 at 23:56

dcolazin · Accepted Answer · 2022-02-04 06:16:01Z

2

The main initial problem was the use of object fields instead of nested fields: only using nested fields is it possible to preserve the structure [{"room", 2}, {"kitchen", 1}], as in object fields the data is flattened to {["room", "kitchen"], [1,2]} without relationships between "room" and 2.

Unluckily, at the moment is not possible to use the SQL API to group by (some?) nested fields, but it is possible to write a native Elastic query using nested aggregations.

answered Feb 4, 2022 at 6:16

dcolazin

1,0862 gold badges15 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Group and count by array of objects' keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related