📌 TIP#1: Extracting values from a JSON_ARRAY using DQL

tracegazer — Thu, 31 Jul 2025 11:42:53 GMT

When analyzing logs or business events that include structured payloads, it’s common to receive data in the form of a JSON_ARRAY. If your field contains this format (for example, under rs.body_result), you can extract specific values from each object in the array.

Here’s a real-world example, where the array contains market data objects with fields like symbol, currency and broadcast_time:

[
  { "symbol": "BNA6C", "currency": "EXT", "closing_price": 103, "broadcast_time": "91844" },
  { "symbol": "BNA6X", "currency": "ARS", "closing_price": 107000, "broadcast_time": "91847" },
  { "symbol": "BN2M6", "currency": "ARS", "closing_price": 0.01, "broadcast_time": "91847" },
  { "symbol": "BU4J6", "currency": "ARS", "closing_price": 101.1, "broadcast_time": "91843" }
]

To extract all broadcast_time values and get the array size, use the following DQL:

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

🎯 Use Case: Finding the latest broadcast time

If your goal is to identify the most recent broadcast (i.e., the highest broadcast_time), simply add:

| fieldsAdd arrayMax(array.broadcast_time), alias: array.broadcast_time_max

This gives you the highest value inside the broadcast_time array.

🧪 Real Example Output (Notebook)

After running the full query in a Dynatrace Notebook, the result looks like this:

array.broadcast_time: [["91840"], ["91834"], ["91838"], ["91821"], ["91842"], ["91841"], ["132548"], ["151414"], ["91822"], ["91843"], ["91841"], ["91842"], ["165520"], ["91843"], ["91842"], ["91820"], ["91824"], ["91820"], ["91821"], ["91820"], ["91843"], ["91840"], ["91841"], ["91829"], ["91845"], ["91815"], ["91845"], ["91829"], ["163735"]]
array.broadcast_time_count: 29
array.broadcast_time_max: 165520

📘 Notes:

This method works on any JSON_ARRAY-compatible field in logs or business events.
You can replace broadcast_time with any other field (like symbol, currency, etc.).
Useful in financial feeds, external API logs, or payload inspection scenarios where the structure is consistent but the array length varies.

Hope this helps streamline your JSON parsing in Dynatrace!

Re: 📌 TIP#1: Extracting values from a JSON_ARRAY using DQL

AntonPineiro — Thu, 10 Jul 2025 09:15:57 GMT

Nice! Thank you!

Re: 📌 TIP#1: Extracting values from a JSON_ARRAY using DQL

GerardJ — Thu, 10 Jul 2025 13:55:16 GMT

Thanks for sharing 🙏

topic Re: 📌 TIP#1: Extracting values from a JSON_ARRAY using DQL in Dynatrace tips

📌 TIP#1: Extracting values from a JSON_ARRAY using DQL

🎯 Use Case: Finding the latest broadcast time

🧪 Real Example Output (Notebook)

📘 Notes:

Re: 📌 TIP#1: Extracting values from a JSON_ARRAY using DQL

Re: 📌 TIP#1: Extracting values from a JSON_ARRAY using DQL