Kibana Discover and DSL query - unknown field [aggs] and unknown field [size]

Hello team, I am currently facing an issue while trying to load DSL query result in the Kibana Discover tool.
The query was created with the help from this forum , which provided the correct result do a NOT IN case. However when trying to load the query in Kibana we are facing the following errors

unknown field [aggs]
unknown field [size]

This is the full details on the error from Kibana front end -

  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "unknown query [aggs]",
        "line": 1,
        "col": 631
      }
    ],
    "type": "x_content_parse_exception",
    "reason": "[1:631] [bool] failed to parse field [filter]",
    "caused_by": {
      "type": "parsing_exception",
      "reason": "unknown query [aggs]",
      "line": 1,
      "col": 631,
      "caused_by": {
        "type": "named_object_not_found_exception",
        "reason": "[1:631] unknown field [aggs]"
      }
    }
  }
}

the query is the following:

{
  "size": 0,
  "aggs": {
    "processing_ids": {
      "terms": {
        "field": "processing_id",
        "size": 10000
      },
      "aggs": {
        "status_processing": {
          "terms": {
            "field": "status_processing"
          },
          "aggs": {
            "latest_record": {
              "top_hits": {
                "size": 1,
                "sort": [
                  {
                    "start_processing": {
                      "order": "desc"
                    }
                  }
                ]
              }
            }
          }
        },
        "processing_id_filter": {
          "bucket_selector": {
            "buckets_path": {
              "docCount": "_count"
            },
            "script": "params.docCount == 1"
          }
        }
      }
    }
  }
}

i could only find one reference that Kibana Visualization does not support aggregation, can you please confirm? otherwise is there any workaround?

NOTE: the query runs perfectly on dev tools all i want to be able to bring the results to my dashboard.

this is a data sample:

POST /processing_records/_bulk
{"index":{}}
{"processing_id":"1234","file_name":"file1.xls","start_processing":"06/07/2024","status_processing":"processing"}
{"index":{}}
{"processing_id":"1234","file_name":"file1.xls","start_processing":"06/07/2024","end_processing":"06/07/2024","status_processing":"processed"}
{"index":{}}
{"processing_id":"1235","file_name":"file2.xls","start_processing":"06/07/2024","status_processing":"processing"}
{"index":{}}
{"processing_id":"1235","file_name":"file2.xls","start_processing":"06/07/2024","end_processing":"06/07/2024","status_processing":"processed"}
{"index":{}}
{"processing_id":"1236","file_name":"file2.xls","start_processing":"06/07/2024","status_processing":"processing"}
{"index":{}}
{"processing_id":"1236","file_name":"file2.xls","start_processing":"06/07/2024","end_processing":"06/07/2024","status_processing":"processed"}
{"index":{}}
{"processing_id":"1237","file_name":"file4.xls","start_processing":"06/07/2024","status_processing":"processing"}

my visualization in Kibana should only bring the processing ID records where the status processing is PROCESSING but does not have a status processing recorded as PROCESSED. In summary, the idea is to present only the records that are in PROCESSING status

Hi @RobertBM,

To confirm, are you adding the above query into the discover filter bar? Do you have KQL or Lucene selected (see below)?

As covered in this older StackOverflow thread, the Discover screen doesn't support aggregations.

Can you share what kind of visualization you're trying to create from this aggregation? You could try something like Aggregation-based visualizations or Vega to build the visualization.

Hope that helps!

1 Like

thanks for your reply @carly.richmond
all im looking is for a way to add a view to a dashboard where the records will have the following sample:

processingid , filename , status_process , start_process , end_process
1234 , file1.csv , processing , 1jul24 , empty
1234 , file1.csv , processed , 1jul24 , 1jul24
1235 , file2.csv , processing , 1jul24 , empty
1235 , file2.csv , processed , 1jul24 , 1jul24
1236 , file3.csv , processing , 1jul24 , empty
1237 , file4.csv , processing , 1jul24 , empty

in the sample above, i want my view to show only process IDs 1236 and 1237 because they are the ones that have a status PROCESSING and NOT a status PROCESSED.

when we do this in a simple SQL i can use a NOT IN clause and we would be done, and the DSL query can do this in dev console, but i could not find a way to build this view in Kibana using Lens or any other features i could find.

hopes this clarifies the solution im looking for.

PS: thanks for the hint about VEGA, but still would like to understand other options because im working with scripted fields which will not work in situation where you want to search an index.

Scripted fields are calculated on-the-fly during data exploration. This means the computed value doesn't exist within the actual Elasticsearch document itself

thanks again

hello team, hope you can give me some pointers on how to apply a solution to this.
thank you

Hi @RobertBM,

Thanks for following up.

all im looking is for a way to add a view to a dashboard where the records will have the following sample

If you're looking to build a table like the above based on your provided query you might be able to create something using a Table-based plot in Vega-Lite by adapting some of these examples. This Vega visualization can then be added to a dashboard.

in the sample above, i want my view to show only process IDs 1236 and 1237 because they are the ones that have a status PROCESSING and NOT a status PROCESSED.

For this logic you could add a bool query to look for the statuses you need and want to exclude, similar to the following (warning I've not executed this):

GET test/_search
{
    "size": 0,
    "query": {
      "bool": {
        "must": [
          {
            "match": {
              "status_processing": "processing"
            }
          }
          ],
          "must_not": [
            {
              "match": {
                "status_processing": "processed"
              }
            }
            ]
      }
    }
    "aggs": {
      // Aggs omitted
    }
}

PS: thanks for the hint about VEGA, but still would like to understand other options because im working with scripted fields which will not work in situation where you want to search an index.

On your point about scripted fields, I did come across this example showing how scripted fields can be used with Vega/ Vega-Lite visualizations that might be worth checking out.

Hope that helps!