Transfrom JSON array to String in table view

[allentsliu]

• What Grafana version and what operating system are you using? 8.1, Mac

• What are you trying to achieve?
  In my elasticsearch, I have documents stored in the following format

{
"start": 1656603585607,
"end": 1656613809357,
"result": false,
"testRuns": [
{
"testEnvironment": null,
"totalSkipped": 0,
"totalFailed": 0,
"totalCount": 172,
"totalPassed": 172,
"totalFlaky": 0,
"flakyTests": {},
"failingTests": [],
"passed": true,
"stopOnFail": true,
"name": "Smoke Tests"
},
{
"testEnvironment": "SDK Environment",
"totalSkipped": 7,
"totalFailed": 2,
"totalCount": 47,
"totalPassed": 40,
"totalFlaky": 3,
"flakyTests": {
"flaky1/": 1,
"flaky2/": 1,
"flaky3/": 1
},
"failingTests": [
"failed1/",
"failed2/"
],
"passed": false,
"stopOnFail": true,
"name": "Sanity"
}
]
}

In the grafana’s table view, the data in the testRuns column has all the data in the array. Is it possible to transform its data to just show the value in the flakyTests and failingTests fields?

I was trying to use the overwrite field → value mapping transform, but it doesn’t seem to transform the data.
Any input is greatly appreciated.

[yosiasz]

Welcome

Seems to be an invalid json body. Can you please fix?

[allentsliu]

Hi Yosiasz, I fixed the json body

[yosiasz]

a bit strange your json properties are expressed differently: one is array the other is not.

			"flakyTests": {
				"flaky1/": 1,
			"flaky2/": 1,
			"flaky3/": 1
			},
			"failingTests": [
				"failed1/",
				"failed2/"
			],

What does this even mean?

"flaky1/": 1,
"flaky2/": 1,
"flaky3/": 1

Seems inconsistent pattern, unless you provided us junk sample

[allentsliu]

The structure is like that in my elasticsearch currently. Is it better to store the value in array or object {}? I can change it to either one which ever is easier to visualize in grafana table.

The value for “flaky1”:1, is the absolute path of the IT test and number of its retry (i.e. “SkylineIT.com.it.http.core.compute/ProfileIT/testApplyProfileForImage/”: 1)
The failingTests field doesn’t have number because it failed after the maximum retry.

I can drop the retry value if it’s causing issue in grafana visualization.

[yosiasz]

I would recommend the following but it is up to you.

"flakytests": [
{"name": "flaky1",
"retry": 1
},
{

"name": }

]

Nice and clean key value.

Best to do it based on best practices of json rather than trying to make grafana happy.

[allentsliu]

Got it… is there a way to show data in those 2 fields instead of the entire json array in the grafana table?

[yosiasz]

Yes. Infinity plugin, json api and others

[allentsliu]

Thanks for the info yosiasz. It seems like the infinity plugin or the json api requires a public api to return the json data.
Does it mean that I can not connect grafana directly to the elasticsearch datasource and I need to write an application to expose the elasticsearch data in a public api for the plugin?
The plugin doesn’t seem to have a place where I can enter the elasticsearch credential or elasticsearch query.
Maybe I am missing something. Do you have an example how the plugin works with elasticsearch?

[yosiasz]

Your es query is your api.

[allentsliu]

Hi yosiasz, I am still stuck with the nested array in the json file. For the following json, I am able to get the first level fields, but I can’t seem to get the fields in the nested array (i.e flakyTests)

{
   "start":1656603585607,
   "end":1656613809357,
   "result":false,
   "testRuns":[
      {
         "testEnvironment":"SDK Environment",
         "totalSkipped":7,
         "totalFailed":2,
         "totalCount":47,
         "totalPassed":40,
         "totalFlaky":3,
         "flakyTests":{
            "flaky1/":1,
            "flaky2/":1,
            "flaky3/":1
         },
         "failingTests":[
            "failed1/",
            "failed2/"
         ],
         "passed":false,
         "stopOnFail":true,
         "name":"Sanity"
      }
   ]
}

Below are my queries. Do you see any issue with my queries? Thanks!

[yosiasz]

this json doesn’t match what you are drilling into in your query. please post full json. for example you have _source whereas the posted json does not have it.

[allentsliu]

Hi yosiasz, below is the full json with 2 main records. Thanks

[yosiasz]

try this

parse-json
| scope "hits.hits"
| project "_source.testRuns"

with following Transformation

For the rest, I will let you fish yourself here on out.

[allentsliu]

Thanks yosiasz for the tips. I will take a look at the UQL and give it a try.

[yosiasz]

also look at json api plugin (see icon). Might be easier

[allentsliu]

I have tried json api, but I can’t figure out how to use it to show fields (i.e. branch and testRuns.flakyTests) in the same table.

[yosiasz]

Have you fully read the documentation of that plugin?