DQL/DPL Transposing Arrays into lines?

[r_weber]

Hey,

I just came across a interesting use case that I'm trying to solve with native DQL/DPL.

Say, I have a bizevent captured content that looks like this:

{
  "jobs": [
    {"name": "job1", "result": "OK", "start": <time>, "end": <time> }
    {"name": "job2", "result": "OK", "start": <time>, "end": <time> }
    {"name": "job3", "result": "OK", "start": <time>, "end": <time> }
    {"name": "job4", "result": "OK", "start": <time>, "end": <time> }
  ]
}

So with one bizevent I get the status of a (unknown) list of jobs, which I actually want to treat independently (create metrics from, as individual bizevents, alert, etc....)

I know that DQL is a table pipe/stream processing so the above content would always be "one row" in this stream.
I'm looking for a creative way how I could extract/action on every list in the "jobs" array.

The only idea I had so far was to create a workflow with a bizevent trigger, that then processes the JSON content and iterates over the array and then ingests a separate bizevent for every item via API (or built in functions if available)?

Do you maybe have more creative ides?

Thanks!

[sinisa_zubic · Accepted solution]

Hi @r_weber 

Do you want the jobs in grail to be stored as seperate record, or is it OK just when querying the bizevents to have the result as a list of jobs?

Because you can easily transform the json into a list of records

data record(content="""{
  "jobs": [
    {"name": "job1", "result": "OK", "start": <time>, "end": <time> }
    {"name": "job2", "result": "OK", "start": <time>, "end": <time> }
    {"name": "job3", "result": "OK", "start": <time>, "end": <time> }
    {"name": "job4", "result": "OK", "start": <time>, "end": <time> }
  ]
}""")
| parse content, "JSON(strict=false):json"
|  expand job=json[jobs]

Best,
Sini

[r_weber]

Thanks @sinisa_zubic ,

that expand command is golden. Wondering why I didn't stumble across it while reading the documentation.
I might still want to create individual BizEvents out of one, but for the initial trials this should work!

Cheers,
Reinhard