Nested queries - query results not visualized

iknezevic · July 18, 2023, 12:54pm

Hi,
I’m using Grafana 9.5.5 (but I also tried this on 10.0.1)

I have documents in elasticsearch which contain a nested property “data” with an array of objects. In short, I’d like to be able to work with those objects as if they were sub-documents. Meaning, create statistics on one of the fields (for example data.documents), while filtering or grouping on another property (data.serverId)

Here is an example of the document I pushed into elasticsearch:
{
“_index”: “my-index-000002”,
“_id”: “3p_taIkBZoWwDWIU8ZAh”,
“_score”: 1,
“_source”: {
“reportingTimestamp”: “2023-06-27T06:35:43.1699003”,
“collectionTimestamp”: “2023-06-27T06:35:43.1699003”,
“correlationID”: “29f5d46d-60e0-42d9-8ca4-5484c1080ecc”,
“entity”: “MyEntity”,
“data”: [
{
“changeID”: “1001”,
“commitStatus”: “Committed”,
“dBOperationType”: “Update”,
“batch”: 20,
“documents”: 3,
“serverId”: 1
},
{
“changeID”: “1002”,
“commitStatus”: “Uncommitted”,
“dBOperationType”: “Update”,
“batchNumber”: 20,
“documents”: 5,
“serverId”: 2
}
]
}
}

Property data is set to be nested in elasticsearch:
…
“data”: {
“type”: “nested”,
“properties”: {
“batch”: {
…

When I try to get plain raw logs with the following expression:

I get the following results (seems fine to me):

However, when I try to do a sum of a nested field (like this):

I get “No data” in the table view. However! And this is where it gets strange. When I inspect the query, I actually see nested aggregated values inside of it:
{
“request”: {
“url”: “api/datasources/proxy/uid/cb4bfb6e-5d83-4546-b47e-8a453a32b518/_msearch?max_concurrent_shard_requests=5”,
“method”: “POST”,
“data”: “{"search_type":"query_then_fetch","ignore_unavailable":true,"index":"my-index-000002"}\n{"size":0,"query":{"bool":{"filter":[{"range":{"collectionTimestamp":{"gte":1687842000000,"lte":1687849200000,"format":"epoch_millis"}}}]}},"aggs":{"3":{"date_histogram":{"field":"collectionTimestamp","min_doc_count":"0","extended_bounds":{"min":1687842000000,"max":1687849200000},"format":"epoch_millis","fixed_interval":"1h"},"aggs":{"2":{"nested":{"path":"data"},"aggs":{"1":{"sum":{"field":"data.documents"}}}}}}}}\n”,
“hideFromInspector”: false
},
“response”: {
“took”: 1,
“responses”: [
{
“took”: 1,
“timed_out”: false,
“_shards”: {
“total”: 1,
“successful”: 1,
“skipped”: 0,
“failed”: 0
},
“hits”: {
“total”: {
“value”: 1,
“relation”: “eq”
},
“max_score”: null,
“hits”:
},
“aggregations”: {
“3”: {
“buckets”: [
{
“2”: {
“1”: {
“value”: 0
},
“doc_count”: 0
},
“key_as_string”: “1687842000000”,
“key”: 1687842000000,
“doc_count”: 0
},
{
“2”: {
“1”: {
“value”: 8
},
“doc_count”: 2
},
“key_as_string”: “1687845600000”,
“key”: 1687845600000,
“doc_count”: 1
},
{
“2”: {
“1”: {
“value”: 0
},
“doc_count”: 0
},
“key_as_string”: “1687849200000”,
“key”: 1687849200000,
“doc_count”: 0
}
]
}
},
“status”: 200
}
],
“$$config”: {
“url”: “api/datasources/proxy/uid/cb4bfb6e-5d83-4546-b47e-8a453a32b518/_msearch?max_concurrent_shard_requests=5”,
“method”: “POST”,
“data”: “{"search_type":"query_then_fetch","ignore_unavailable":true,"index":"my-index-000002"}\n{"size":0,"query":{"bool":{"filter":[{"range":{"collectionTimestamp":{"gte":1687842000000,"lte":1687849200000,"format":"epoch_millis"}}}]}},"aggs":{"3":{"date_histogram":{"field":"collectionTimestamp","min_doc_count":"0","extended_bounds":{"min":1687842000000,"max":1687849200000},"format":"epoch_millis","fixed_interval":"1h"},"aggs":{"2":{"nested":{"path":"data"},"aggs":{"1":{"sum":{"field":"data.documents"}}}}}}}}\n”,
“hideFromInspector”: false
}
}
}

Same thing happens no matter what I do. I have a query to also filter out to only use serverId:1:

I still get “No data” in the table view, however, the query inspector shows the information:
{
“request”: {
“url”: “api/datasources/proxy/uid/cb4bfb6e-5d83-4546-b47e-8a453a32b518/_msearch?max_concurrent_shard_requests=5”,
“method”: “POST”,
“data”: “{"search_type":"query_then_fetch","ignore_unavailable":true,"index":"my-index-000002"}\n{"size":0,"query":{"bool":{"filter":[{"range":{"collectionTimestamp":{"gte":1687842000000,"lte":1687849200000,"format":"epoch_millis"}}}]}},"aggs":{"3":{"date_histogram":{"field":"collectionTimestamp","min_doc_count":"0","extended_bounds":{"min":1687842000000,"max":1687849200000},"format":"epoch_millis","fixed_interval":"1h"},"aggs":{"4":{"nested":{"path":"data"},"aggs":{"5":{"filters":{"filters":{"data.serverId:1":{"query_string":{"query":"data.serverId:1","analyze_wildcard":true}}}},"aggs":{"1":{"sum":{"field":"data.documents"}}}}}}}}}}\n”,
“hideFromInspector”: false
},
“response”: {
“took”: 2,
“responses”: [
{
“took”: 2,
“timed_out”: false,
“_shards”: {
“total”: 1,
“successful”: 1,
“skipped”: 0,
“failed”: 0
},
“hits”: {
“total”: {
“value”: 1,
“relation”: “eq”
},
“max_score”: null,
“hits”:
},
“aggregations”: {
“3”: {
“buckets”: [
{
“4”: {
“5”: {
“buckets”: {
“data.serverId:1”: {
“1”: {
“value”: 0
},
“doc_count”: 0
}
}
},
“doc_count”: 0
},
“key_as_string”: “1687842000000”,
“key”: 1687842000000,
“doc_count”: 0
},
{
“4”: {
“5”: {
“buckets”: {
“data.serverId:1”: {
“1”: {
** “value”: 3**
** },**
** “doc_count”: 1**
}
}
},
“doc_count”: 2
},
“key_as_string”: “1687845600000”,
“key”: 1687845600000,
“doc_count”: 1
},
{
“4”: {
“5”: {
“buckets”: {
“data.serverId:1”: {
“1”: {
“value”: 0
},
“doc_count”: 0
}
}
},
“doc_count”: 0
},
“key_as_string”: “1687849200000”,
“key”: 1687849200000,
“doc_count”: 0
}
]
}
},
“status”: 200
}
],
“$$config”: {
“url”: “api/datasources/proxy/uid/cb4bfb6e-5d83-4546-b47e-8a453a32b518/_msearch?max_concurrent_shard_requests=5”,
“method”: “POST”,
“data”: “{"search_type":"query_then_fetch","ignore_unavailable":true,"index":"my-index-000002"}\n{"size":0,"query":{"bool":{"filter":[{"range":{"collectionTimestamp":{"gte":1687842000000,"lte":1687849200000,"format":"epoch_millis"}}}]}},"aggs":{"3":{"date_histogram":{"field":"collectionTimestamp","min_doc_count":"0","extended_bounds":{"min":1687842000000,"max":1687849200000},"format":"epoch_millis","fixed_interval":"1h"},"aggs":{"4":{"nested":{"path":"data"},"aggs":{"5":{"filters":{"filters":{"data.serverId:1":{"query_string":{"query":"data.serverId:1","analyze_wildcard":true}}}},"aggs":{"1":{"sum":{"field":"data.documents"}}}}}}}}}}\n”,
“hideFromInspector”: false
}
}
}

I have no idea what (if anything) I’m doing wrong.

Please help,

Ivan

nikhilmaurya10 · August 1, 2024, 6:49am

Hi @iknezevic have you found any solution to your problem? I am quite stuck with the same issue: I cannot generate time series when using Nested Field grouping at the last level. It shows some data in the table but not as a time series.

yosiasz · August 1, 2024, 5:28pm

Welcome @nikhilmaurya10 @iknezevic

please share your json data resulting from the es search

nikhilmaurya10 · August 2, 2024, 9:03am

Sample 1 - The date histogram is the last item in the Query editor
Query Generated-

{"size":0,"query":{"bool":{"filter":[]}},"aggs":{"8":{"date_histogram":{"field":"ordered_at","min_doc_count":"0","extended_bounds":{"min":1706863473970,"max":1722588273970},"format":"epoch_millis","interval":"1d"},"aggs":{"9":{"nested":{"path":"items"},"aggs":{"10":{"terms":{"field":"items.name","size":10,"order":{"_key":"desc"},"min_doc_count":1},"aggs":{}}}}}}}}

Result-

{"aggregations":{"8":{"buckets":[{"9":{"10":{"doc_count_error_upper_bound":0,"sum_other_doc_count":0,"buckets":[{"key":"Milk Tea","doc_count":1},{"key":"kopi","doc_count":1}]},"doc_count":2},"key_as_string":"1719705600000","key":1719705600000,"doc_count":2}]}}}

Sample 2 - Date histogram is applied before the Nested agg in the Query editor
Query Generated-

{"size":0,"query":{"bool":{"filter":[]}},"aggs":{"9":{"nested":{"path":"items"},"aggs":{"10":{"terms":{"field":"items.name","size":10,"order":{"_key":"desc"},"min_doc_count":1},"aggs":{"11":{"date_histogram":{"field":"ordered_at","min_doc_count":"0","extended_bounds":{"min":1706863635430,"max":1722588435430},"format":"epoch_millis","interval":"1d"},"aggs":{}}}}}}}}

Result-

{"aggregations":{"9":{"10":{"doc_count_error_upper_bound":0,"sum_other_doc_count":83,"buckets":[{"11":{"buckets":[{"key_as_string":"1706832000000","key":1706832000000,"doc_count":0},{"key_as_string":"1706918400000","key":1706918400000,"doc_count":0}]},"key":"Milk Tea","doc_count":1}]}}}}

In both cases, I am getting data in the table but there is Data is missing a time field error when I use a time-series graph

Additionally-
In my es doc, the items is a nested field and I am grouping items by items.name property over time. A simple usage case is to count the number of items(for each item) for one day and plot a graph.

Also since I cannot rebase my grafana code to the latest, I have manually pulled this PR in my code base and verified it works as the newest grafana code on GitHub.

yosiasz · August 2, 2024, 10:19am

use the convert field type transformation to convert the date field to time

nikhilmaurya10 · August 2, 2024, 10:50am

Even after doing this there is no plot, I guess it’s because the date is 1979 and the grafana time range max can go to last year?

yosiasz · August 2, 2024, 10:55am

what does the raw date in es look like?

nikhilmaurya10 · August 2, 2024, 11:13am

It’s in this form

{
    "_index": "orders_2024-07",
    "_type": "_doc",
    "_id": "G1962528dsadasd",
    "_score": 1.0,
    "_source":
    {
        "ordered_at": "2024-06-30T20:06:10Z",
        "order_number": "F-68720817209",
        "items":
        [
            {
                "variants":
                [],
                "quantity": 1,
                "promo_id": "",
                "price": 10000.0,
                "note": "",
                "name": "Milk Tea",
                "id": "21f4fb3a-1a69-4df8-bd5a-4edf66b33a97"
            }
        ]
    }
}

nikhilmaurya10 · August 2, 2024, 11:25am

If I change the time range there is a graph but not sure what it represents, the value on y axis count is 14, which is max value of one item in the table above

yosiasz · August 2, 2024, 1:30pm

what you see is accurate for the data sample you provided

nikhilmaurya10 · August 2, 2024, 1:47pm

But I want to see line graphs for each items, I am not sure how to get a plots for each items.name terms? Each es doc contains this items list which contains different items list each item has name and count which I’ll sum to get the final number.

And then the issue is of timestamp how is it converted to year 1970? And why is every timestamp is of exact same time?

yosiasz · August 2, 2024, 4:25pm

the sample data you posted only has one items array, so there can be n+1 items?

nikhilmaurya10 · August 2, 2024, 4:57pm

Yes and order can have multiple items. That’s why it’s a list. For a simple case we can focus on only one item at once. But ultimately I would need it to work for multiple items case.

yosiasz · August 2, 2024, 6:26pm

try to use infinity plugin instead, much easier to work with things

https://try.jsonata.org/JZfzjdhGu

nikhilmaurya10 · August 2, 2024, 6:44pm

Unfortunately I cannot do that. Since this data is from a center es datastore which needs to be used for this kind of things. I’ll have to find some way to modify the code to handle this or maybe change the data structure from nested to a simple, but I will have to use ES only.

yosiasz · August 2, 2024, 7:14pm

you still use es just a different plugin to get the data. ip.of.es:9200 search for that type of solution in forum