How to read arrays from JSON using Infinity datasource plugin

[tusharkesarwani2]

I used the Infinity plugin to make a call to REST API.
The following is the JSON response with timestamp (epoch) and values.

{
  "report": [
    {
      "timeWindow": {
        "entityEpochSecUTC": 1625788800,
        "entityTimeDurationSec": "SECOND",
        "entityTimeField": "kafka_ingestion_time"
      },
      "entity": {
        "entityType": "hive",
        "entityNamespace": "db",
        "entityName": "b_table",
        "entityInstance": "server-ams4",
        "dataAssetRef": "hive.db.b_table-v_1.0"
      },
      "reportItems": [
        {
          "checkName": "Check for cc info",
          "checkLevel": "Error",
          "checkStatus": "Success",
          "constraintName": "containsCreditCardNumber",
          "constraintStatus": "Success",
          "constraintMessage": "string"
        }
      ]
    }
  ]
}

I am unable to get the values under reportItems. I tried to do reportItems.checkStatus , reportItems[].checkStatus and reportItems[1].checkStatus

Apparently reportItems[1].checkStatus do work in Grafana, however i need it to get all the records and and not just the first record.

Any help would be great.

[curvaton]

Hi, I guess the answer is a bit late but I’ll give you an answer anyway, for the record. You can do do it in two ways with the infinity datasource:

• using JSONPath:
  
  
• using UQL language:
  
  
  
  Hope this helps.

[yesoreyeram]

you can also use mv-expand to expand the array items in to row

parse-json
| scope "report"
| mv-expand "reportItem"="reportItems"
| project "ts"=unixtime_seconds_todatetime("timeWindow.entityEpochSecUTC"), "type"="entity.entityType", "check"="reportItem.checkName"

[gmgangamanj]

if we have a data which does not have “root” element,[{“date”:“2023-04-21T14:21:48.1092831+05:30”,“temperatureC”:47,“temperatureF”:116,“summary”:“Freezing”},{“date”:“2023-04-22T14:21:48.1092864+05:30”,“temperatureC”:20,“temperatureF”:67,“summary”:“Mild”},{“date”:“2023-04-23T14:21:48.1092867+05:30”,“temperatureC”:22,“temperatureF”:71,“summary”:“Hot”},{“date”:“2023-04-24T14:21:48.1092869+05:30”,“temperatureC”:30,“temperatureF”:85,“summary”:“Balmy”},{“date”:“2023-04-25T14:21:48.1092871+05:30”,“temperatureC”:14,“temperatureF”:57,“summary”:“Bracing”}] how can we parse such JSON using query languages (especially , if is the response obtained through an API)

[yosiasz]

Welcome @gmgangamanj

You can just simple use UQL

[
	{
		"date": "2023-04-21T14:21:48.1092831+05:30",
		"temperatureC": 47,
		"temperatureF": 116,
		"summary": "Freezing"
	},
	{
		"date": "2023-04-22T14:21:48.1092864+05:30",
		"temperatureC": 20,
		"temperatureF": 67,
		"summary": "Mild"
	},
	{
		"date": "2023-04-23T14:21:48.1092867+05:30",
		"temperatureC": 22,
		"temperatureF": 71,
		"summary": "Hot"
	},
	{
		"date": "2023-04-24T14:21:48.1092869+05:30",
		"temperatureC": 30,
		"temperatureF": 85,
		"summary": "Balmy"
	},
	{
		"date": "2023-04-25T14:21:48.1092871+05:30",
		"temperatureC": 14,
		"temperatureF": 57,
		"summary": "Bracing"
	}
]

[gmgangamanj]

Thanks for it. I too could do by directly providing the JSON and UQL as in Figure

. But when i provided an URL , no data is been shown as in Figure

. So if you could get me clarity on the data source plugin you have used and its configuration . It would be too helpful. Thank you once again

[gmgangamanj]

It is working now. It is working with localhost URL , once I have reestablished the localhost API connection. Thanks for the help @yosiasz