Aggregate multiple records to single chart

Elastic Stack Kibana
1

Hi,
The API I'm using holds nested json data (i.e, base-derived nodes relation)
When index it in ES, it's flat-json index
What I'm trying to do simply is to aggregate base object with derived object into single chart

E.g.
Base object - x
derived object - y (nested in object x)
Display in bar chart all records for objects as single column. Not multiple column
I want to use Lucene's syntax in my dashboard

Any idea?
10x in advance

2

Hi Avivc,

Could you please post a very simple example data set here (just a couple of docs) in the form that can be pasted into the Kibana dev console so we could load it and try to create the chart you're looking for?

Thanks,
Lee

3

Hi @LeeDr ! Yes of course
See the next:

1544×799 16.4 KB

The 2nd bar in the base object and all other bars are the derived bars. I want to display single bar
My syntax in the search bar is;

Project.name: ("base object" OR "derived objects")

ES + KIbana: 5.4.0 (maybe create scripted field? )

Tnx !

4

Maybe this post will help you. In this example, the user has data that might be in 3 different fields but they want one field they can search on. So if you create a scripted field that gets either the Project.name: base object or Project.name: dereved object into one string field you can aggregate on that.

5

Hi @LeeDr
I'm trying to create query to aggregate data (bucket nested aggregation) to put in the scripted field but without success.
The syntax in painless

Any suggestions?

6

Hi Avivc,

If I understand you correctly, you can't do that. Scripted fields only access data in individual documents, not aggregations across multiple documents.

Regards,
Lee

7

Tnx @LeeDr
What I'm trying is to create is the next script (using Dev Tools) :

GET kpi/Defect/_search
{
"aggs": {
"NAME": {
"AGG_TYPE": {}
}
}
}

I want to create this script to aggregate data for the base object and it's derived (by key). With that, I want create my scripted field.
What do you mean by individual documents ?

8

Hi @LeeDr
If it's not possible via scripted fields, are there other solutions?
Does value count aggregation can be efficient in this case?

9

Hi Avivc,
I'm sorry but I don't think I can help without some kind of example data we can talk about. Can you post some code we could paste into the dev console to create a few documents that would represent your data to use as an example?

Thanks,
Lee

10

Hi @LeeDr
I have a field in my index pattern named : Project._refObjectName
I wish to aggregate various values refer to this field

E.g.:
Values name: field1, field2, field3

I want to aggregate this values like described here
Something like this:

POST /myIndex/_search?size=0
{
"aggs" : {
"grades_count" : {
"value_count" : {
"script" : {
"params" : {
"field" : "type"
}
}
}
}
}
}

I'm not sure how to set the aggregation I wish to have using this script

Edit:
If I do the simple query:

GET myIndex/_search
{
"query": {
"match": {
"Project._refObjectName": {
"query": "myValue"
}
}
}
}

I get the total count for this specific value. All I want it to aggregate multiple values (or using wlid card if possible) and fetch the total count

Is it enough data to move on ? is this code is sufficient?

10x a lot !

11

Hi Avivc,

In the Fields aggregation or field formatter? link I posted above I included commands to put a couple of documents using the dev console, and then I used those documents to answer a question. Can you create a couple of sample documents like that and post it here and then what you're trying to aggregate out of that? I can't tell what you're data looks like from your searches.

Thanks,
Lee

12

Hi @LeeDr

I'm trying too - but regarding to your link, which is great and very helpful, I need to use PUT for specific field in my documents and not new fields
This field is under a specific type. So when I'm trying to do:

PUT myIndex/myType/1
{
"Project._refObjectName" : "baseObejct/ derivedObject"
}

I'm not getting what I wish for.
Maybe I should add another key:value pair/s to my query?

13

I used PUT in my examples but you don't have to.

POST can be used to achieve auto-generation of ids whereas a PUT is used when you want to specify an id.

14

Hi @LeeDr
I want to simplify it - just taking couple of values for existing key and create scripted field that'll aggregate it to single Object so I can store it in single bar chart. Not related to nested objects

I've tried to follow the steps from your example but no success.

Tnx

15

@LeeDr any idea please ?
tnx :thumbsup:

16

Hi Avivc,

I'm sorry but I still don't understand the data in your docs. I suggested you provide sample data but your single PUT myIndex/myType/1 isn't enough for me to understand your problem.

Can you please provide a couple of sample docs and then explain which field you're trying to aggregate?

Thanks,
Lee

17

Hi @LeeDr
The next is part of my json input data that's relevant for my aggregation:

"Project": {
"_type": "Project",
"_rallyAPIMinor": "0",
"_rallyAPIMajor": "2",
"_refObjectName": "Alt-st-aviv avivScrum",
"_ref": "https://data",
"_refObjectUUID": "uniqueGuid"
},

The relevant field is Project._refObjectName
I want to take couple of values for this field into single scripted field

In the Discover I use to do:

Project._refObjectName: ("value1" OR "value2")

But, as the above image display, it gives me more than 1 bar, which I don't want in some cases

Is this the data you need?
Tnx a lot :thumbsup:

18

I think you're saying that the _refObjName field can have some set of values and you want to create a scripted field that combines some of those. So maybe you can create a scripted field like this;
(I just copied this from another post and changed the field name)

NOTE: I broke it into multiple lines to make it easier to read, but it might have to all be on one line for the scripted field.

if (doc["_refObjName"].value == 'Windows Server' || 
    doc["_refObjName"].value == 'Linux Server' || 
    doc["_refObjName"].value == 'PCI-Devices')  {
   return "OS";
} else if (doc["_refObjName"].value == 'Palo Alto Firewall Group' || 
    doc["_refObjName"].value == 'ASA Firewall') {
    return "Firewall";
}

When creating a new scripted field, I would always start with a very simple case and make sure it works in Discover first. And then go back and add more to it. So maybe something like this to start with;

if (doc["_refObjName"].value == 'Alt-st-aviv avivScrum') {
   return "matches";
} else {
   return "no match";
}
19

Tnx @LeeDr
When I'm creating a very slim scripted field with 1 item (for testing in Discover), I'm getting the next error:
"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [Project._refObjectName] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."

I can see that this field is analyze field (text) in Kibana's management
I found this tutorial, but here too, when trying to modify the field like this:

PUT my_index/_mapping/Defect
{
"Defect": {
"properties": {
"Project._refObjectName": {
"type": "text",
"fielddata": true
}
}
}
}

I'm getting the next error:
"type": "illegal_argument_exception", "reason": "Mapper for [Project._refObjectName] conflicts with existing mapping in other types:\n[mapper [Project._refObjectName] is used by multiple types. Set update_all_types to true to update [fielddata] across all types.]"

Should I add update_all_types == true ? If so, where? or I'm not in the right direction for solution ?

10x in advance :thumbsup:

20

Are you setting the my_index mapping after docs have been indexed already? If so, you might have to delete the docs, set the mapping, and then load the docs again.

In the console, you can just do DELETE my_index. This is assuming this is test data that you can reload.

Regards,
Lee