topic DQL/DPL Transposing Arrays into lines? in Developer Q&A Forum

DQL/DPL Transposing Arrays into lines?

r_weber — Sat, 02 Dec 2023 14:49:35 GMT

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!

Re: DQL/DPL Transposing Arrays into lines?

sinisa_zubic — Mon, 04 Dec 2023 08:29:20 GMT

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

Re: DQL/DPL Transposing Arrays into lines?

r_weber — Mon, 04 Dec 2023 19:23:34 GMT

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