Json parsing in DQL

[brett_schubach1]

Hi All, 

I need some help as I am trying to extract the below name : value pairs from a bizevent that I have setup but can't seem to be able to do this?

{ "details": [ { "timestamp": "2020-04-05T18:05:21+05:30", "talktome": "", "type": "issues", "Metrics": [ { "name": "PLATFORM", "value": "windows" }, { "name": "HOST", "value": "1111111" }, { "name": "VERSION", "value": "100" } ] } ] }

{ "details": [ { "timestamp": "2020-04-05T18:05:21+05:30", "talktome": "", "type": "issues", "Metrics": [ { "name": "PLATFORM", "value": "ios" }, { "name": "HOST", "value": "1111111" }, { "name": "VERSION", "value": "100" } , { "name": "SDK", "value": "1.23" }] } ] }

Please could someone assist with a query that can pull the Metrics name : value pair whereby the Metrics name value on both the examples above will be satisfied

Example 

PLATFORM = windows , HOST = 1111111, VERSION=100

PLATFORM = ios, HOST = 1111111, VERSION=100, SDK=1.23

[cesarsaravia · Accepted solution]

Hi @brett_schubach1 
I really don't understand what exactly you want to get. In this example, with the same data, I could get all the values on new fields.

data record(data="""{"details":[{"timestamp": "2020-04-05T18:05:21+05:30","event_1": "","type": "business","Metrics": [ { "name": "PLATFORM", "value": "windows" }, { "name": "HOST", "value": "1111111" }, { "name": "VERSION", "value": "100" } ] } ]}"""),
     record(data="""{ "details": [ { "timestamp": "2020-04-05T18:05:21+05:30", "talktome": "", "type": "issues", "Metrics": [ { "name": "PLATFORM", "value": "ios" }, { "name": "HOST", "value": "1111111" }, { "name": "VERSION", "value": "100" } , { "name": "SDK", "value": "1.23" }] } ] }""")
| parse data, "JSON:json"
| fieldsFlatten json
| fields timestamp = json.details[0][timestamp],type =  json.details[0][type], metrics=json.details[0][Metrics]
| fieldsAdd metrics_str = toString(metrics)
| expand metrics
| fieldsAdd name=metrics[name],value=metrics[value]

Results:

I think if you want to get all those values on the same field, maybe you need to user the KVP (Key value pairs) Pattern.

Regards,

[brett_schubach1]

Hi @cesarsaravia 

Thank you for the above will try it out. You are correct I want all the data per record on the same line in the table. Appreciate the assistance and guidance. 

[krzysztof_hoja]

KVP is something to use, but due to what formatting valid JSON allows it is hard to express the pattern in the way it will always work independently e.g. how many blanks or new lines are between elements. I tried to be as generic as possible with it (but still assumed that "name" comes before "value"):

data record(data="""{"details":[{"timestamp": "2020-04-05T18:05:21+05:30","event_1": "","type": "business","Metrics": [ { "name": "PLATFORM", "value": "windows" }, { "name": "HOST", "value": "1111111" }, { "name": "VERSION", "value": "100" } ] } ]}"""),
     record(data="""{ "details": [ { "timestamp": "2020-04-05T18:05:21+05:31", "talktome": "", "type": "issues", "Metrics": [ { "name": "PLATFORM", "value": "ios" }, { "name": "HOST", "value": "1111111" }, { "name": "VERSION", "value": "100" } , { "name": "SDK", "value": "1.23" }] } ] }""")
| parse data, "DATA '\"Metrics\":' SPACE* '[' KVP{ SPACE* '{' SPACE* DQS ':' SPACE* DQS:key SPACE* ',' SPACE* DQS ':' SPACE* DQS:value SPACE* '}' SPACE* (','|']')}:metrics"

Kris