How to get max KEY:VALUE per event.id without using expand (JSON array)

[tracegazer]

Hi everyone,

I’m working with Business Events in Dynatrace and facing an issue I can't solve. I have a string field named rs.body_result that contains a JSON_ARRAY of objects. Each object includes a broadcast_time key (numeric long). Example:

[
  {"keyA": "valueA", "broadcast_time": 123456},
  {"keyA": "valueA", "broadcast_time": 153824},
  ...
]

What I need:

• Get the maximum broadcast_time value,
• Per event.id,
• Without generating duplicated rows or breaking pipeline compatibility.

The only working solution so far is:

fetch bizevents
| parse rs.body_result, """JSON_ARRAY:parsed_json_elements"""
| expand parsed_json_elements
| fieldsFlatten parsed_json_elements
| fields event.id, time = parsed_json_elements[broadcast_time]
| summarize max(time) by: {event.id}

This works, but expand creates one row per element in the array, which duplicates events and cannot be used in pipelines, where expand is not allowed.

Is there any way to get the maximum broadcast_time per event.id without using expand?

Any ideas, workarounds or suggestions would be really appreciated.
I’ve attached a sample rs.body_result with dummy data.

Thanks in advance!

[Dant3 · Accepted solution]

Problem is that if you dont know how many objects you have in the content, hard to do. You can´t do a loop/while in DQL.

You could do something like 

| fieldsadd datos[1][broadcast_time], alias: valor1
| fieldsadd datos[2][broadcast_time], alias: valor2
| fieldsadd datos[3][broadcast_time], alias: valor3
| fieldsadd datos[4][broadcast_time], alias: valor4
| fieldsadd array(valor1,valor2,valor3,valor4), alias: array_valores
| fieldsAdd arrayMax(array_valores)
| fieldsadd arraySize(datos)

And avoid expand, you would need to hardcode the fieldsadd. 

Result:

[tracegazer · Accepted solution]

Hi, your comment was really helpful — I found a way to iterate through the array, which I honestly had no idea how to approach before, but your DQL example pointed me in the right direction.

| parse rs.body_result, """JSON_ARRAY:parsed_json_elements"""
| fieldsAdd array(parsed_json_elements[][broadcast_time]), alias: MAX
| fieldsAdd arraySize(parsed_json_elements), alias: COUNT_MAX

Thank you, I really appreciate it!

[Dant3]

Now that is a solution I didn't thought about. Passing the first index value empty