Parsing JSON containing array list. Need help in extract fields.

[satishpaidisett]

Hi Team,

I am trying to parse json containing array list but having an issue in extracting fields. I have pasted two records here.

Below is the sample content:-

{

  "event": {

    "header": {

      "mod1": "xxxx",

      "mod2": "yyyy",

      "mod3": 54433,

    },

    "details": [

      {

        "element": "name",

        "pastval": null,

        "presentval":yyyyyy

      },

      {

        "element": "code",

        "oldValue": null,

        "newValue": 24

      }

]

  },

  "eventid": {

    "id": "3111366"

  }

}

{

  "event": {

    "header": {

      "mod1": "tytryeter",

      "mod2": "yhhfjhfjhf",

      "mod3": 54432,

    },

    "details": [

      {

        "element": "name",

        "pastval": null,

        "presentval":xxxxxxxx

      },

      {

        " element ": "code",

        " pastval ": null,

        " presentval ": 25

      }

]

  },

  "eventid": {

    "id": "3111367"

  }

}

[tracegazer]

Hello, could you please confirm if this tip resolves your concern?

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

[satishpaidisett]

No the above is not helping for me.

We need to do average of all the values of "presentval" with "element" as "code" which exists in array list. 

[dannemca]

You can use the 'expand' command, https://docs.dynatrace.com/docs/discover-dynatrace/references/dynatrace-query-language/commands/stru... 

Example:

...
| expand  details = event[details]
| fieldsAdd element = details[element], presentval = details[presentval]

with this, you will have the new fields 'element' and 'presentval' to play with for each json entry.

Try and let us know. 

[satishpaidisett]

No, this is not working. My Json is below. I need to calculate the sum of all new values which contains element as "amount"

{
"businessprocess": {
"header": {
"moduleName": "xxxxx",
"actionType": "RECORD",

},
"details": [
{
"element": "NAME",
"old": "care",
"new": "care1"
},
{
"element": "amount",
"old": "30",
"new": "40"
}]}
"businessEventId":{"id":"234"}}

{
"businessprocess": {
"header": {
"moduleName": "yyyyy",
"actionType": "RECORD",

},
"details": [
{
"element": "NAME",
"old": "caring1",
"new": "caring2"
},
{
"element": "amount",
"old": "50",
"new": "60"
}]}
"businessEventId":{"id":"123"}}

[dannemca]

I tried to simulate the DQL in demo env and just got the result as following:

data json:"""[{
"businessprocess": 
{
"header": 
{
"moduleName": "xxxxx",
"actionType": "RECORD"
},
"details": [
{
"element": "NAME",
"old": "care",
"new": "care1"
},
{
"element": "amount",
"old": 30,
"new": 40
}]},
"businessEventId":{"id":"234"}},
{
"businessprocess": {
"header": {
"moduleName": "yyyyy",
"actionType": "RECORD"
},
"details": [
{
"element": "NAME",
"old": "caring1",
"new": "caring2"
},
{
"element": "amount",
"old": 50,
"new": 60
}]},
"businessEventId":{"id":"123"}}]"""
| expand  details = businessprocess[details]
| fieldsadd element = details[element], new = details[new]
| filter element == "amount"
| summarize totalsum = sum(toDouble(new)), by:{element}

Result:

Could you try at your end?