eazyBI REST API data contains nest arrays

Questions & Answers
1

I am ingesting a set of data using the REST API connector and am struggling with nest JSON objects.

Example of the data

{
“aggregations” : {
“2” : {
“buckets” : [
{
“key” : “source-data-1”,
“doc_count” : 33,
“3” : {
“buckets” : [
{
“key” : “User-1”,
“doc_count” : 1350
},
{
“key” : “User-2”,
“doc_count” : 900
},
{
“key” : “User-3”,
“doc_count” : 345
}
]
}
}
}
}

As you can see, there are two arrays:

  • aggregations.2.buckets[*]
  • aggregations.2.buckets[].3.buckets[]

I can manage to get the first array in using the Data Path “$.aggregations.2.buckets[*]”, but the second array can then not be assigned to metrics (and is greyed out in the subsequent page listing it as an “array”).

How can I flatten the second array and include that?

2

Hi @Bjorn,

Welcome to the eazyBI community!
You could try using custom Javascript in Rest API import configuration and remove any data path.
Try something like this example of Javascript to aggregate results from both level buckets in the same level.

result = [];
_.each(aggregations.2.buckets, function(bucket2) {
  _.each(bucket2.3.buckets, function(bucket3) {
    result.push(
      {
        key_2: bucket2.key,
        doc_count_2: bucket2.doc_count,
        key_3: bucket3.key,
        doc_count_3: bucket3.doc_count
      }
    );
  });
});
return result;

Martins / eazyBI team

3

Thanks Martins.

I tried your code, but am getting an error “Execution of custom JavaScript code raised the following error:
missing ) after argument list” (might be a Javascript syntax problem, but I am no coder unfortunately).

Looking at what it will do, adding everything to a single line is maybe not the best way forward. I’d be more interested in having a solution that created new entries i.e.

Record#1
key (to be renames to “data source”)=source-data-1
key(rename field to “user”)=User-1
doc-count=1350

Record#2
key (to be renames to “data source”)=source-data-2
key(rename field to “user”)=User-2
doc-count=900

Record#3
key (to be renames to “data source”)=source-data-3
key(rename field to “user”)=User-3
doc-count=345

The doc_count=33 (actually wrong - should have been 1350+900+345) can also be included for each of the above lines.

The idea in any case is to loop through each of the nested array values and create a new line with lower level data (what SQL UNNEST would do)

4

@Bjorn

Try this version:

result = [];
_.each(aggregations["2"].buckets, function(bucket2) {
  _.each(bucket2["3"].buckets, function(bucket3) {
    result.push(
      {
        key_2: bucket2.key,
        doc_count_2: bucket2.doc_count,
        key_3: bucket3.key,
        doc_count_3: bucket3.doc_count
      }
    );
  });
});
return result;

Using this code above the nested array values should be extracted as separate lines.

Martins / eazyBI