Joining two API queries with key:value pair and join on "key" field

Grafana
,
1

Hello,

We have two queries using JSON API datasource and both returns data in JSON format as below;
Query-1
{“1”:100,“2”:27.7717391305,“3”:214,“4”:28,“5”:0,“6”:25.104,“7”:28807.407,“8”:-348201.3876}

image
image1276×389 31.9 KB

Query-2
{“id”:1,“n”:“Device Battery”,“t”:“custom”,“d”:“”,“m”:“%”,“p”:“io_113”,“f”:0,“c”:“{"act":0,"appear_in_popup":true,"ci":{},"cm":1,"mu":0,"pos":6,"show_time":false,"timeout":0}”,“vt”:0,“vs”:0,“tbl”:,“ct”:1695125877,“mt”:1695125877}
{“id”:3,“n”:“Fuel Tank Capacity”,“t”:“custom”,“d”:“”,“m”:“l”,“p”:“const214”,“f”:0,“c”:“{"act":1,"appear_in_popup":true,"ci":{},"cm":1,"mu":0,"pos":3,"show_time":false,"timeout":0}”,“vt”:0,“vs”:0,“tbl”:,“ct”:1695125877,“mt”:1695213070}

image
image1279×600 47.7 KB

In “Query-1” result, values coming in “key” field of the jason is actually an id which needs to join with “Query-2”.“id” field. We are not able to join it since id field is coming as key:value pair in Query-1.

Expected output:
image

Please advice.

2

Can you clean up the second json data sample? It says not valid json

{"id":1,"n":"Device Battery","t":"custom","d":"","m":"%","p":"io_113","f":0,"c":"{"act":0,"appear_in_popup":true,"ci":{},"cm":1,"mu":0,"pos":6,"show_time":false,"timeout":0}","vt":0,"vs":0,"tbl":[],"ct":1695125877,"mt":1695125877}
{"id":3,"n":"Fuel Tank Capacity","t":"custom","d":"","m":"l","p":"const214","f":0,"c":"{"act":1,"appear_in_popup":true,"ci":{},"cm":1,"mu":0,"pos":3,"show_time":false,"timeout":0}","vt":0,"vs":0,"tbl":[],"ct":1695125877,"mt":1695213070}

image

4

Please post properly fornatted json data and please validate before posting?

5

Hello Yosiasz,

It’s strange that you might be getting some issues with JSON data but that is what we are receiving from the pai call. Let me finetune the response.

QUERY-1 Result:
{
“1”: 100,
“2”: 78.3448275857,
“3”: 214,
“4”: 32,
“5”: 1,
“6”: 23.117,
“7”: 28807.694,
“8”: -348201.3876
}

QUERY-2 Result:
{
“items”: [
{
“sens”: {
“1”: {
“id”: 1,
“n”: “Device Battery”,
“t”: “custom”,
“d”: “”,
“m”: “%”
},
“2”: {
“id”: 2,
“n”: “Fuel Level”,
“t”: “fuel level”,
“d”: “”,
“m”: “l”
},
“3”: {
“id”: 3,
“n”: “Fuel Tank Capacity”,
“t”: “custom”,
“d”: “”,
“m”: “l”
},
“4”: {
“id”: 4,
“n”: “Fuel Tank Temperature”,
“t”: “temperature”,
“d”: “”,
“m”: “°C”
},
“5”: {
“id”: 5,
“n”: “Ignition”,
“t”: “engine operation”,
“d”: “”,
“m”: “On/Off”
},
“6”: {
“id”: 6,
“n”: “Main Power”,
“t”: “voltage”,
“d”: “”,
“m”: “V”
},
“7”: {
“id”: 7,
“n”: “Odometer”,
“t”: “mileage”,
“d”: “”,
“m”: “km”
},
“8”: {
“id”: 8,
“n”: “Mileage (Yesterday)”,
“t”: “mileage”,
“d”: “”,
“m”: “km”
}
}
}
]
}

Expected Output:

Query-2 represent the master data for the sensors with sensor id (items.sens.[].id) and sensor name (items.sens.[].n). Query-1 represent the current value of the sensors. We need to join Query-1 & Query-2 using sensor ID but unfortunately we are not able to interpret id in Query-1 as a column and so we are not able to join.

Hope I am able to explain my case now.

1 Like
6

Please post properly fornatted json

"sens": {
"1": {
"id": 1,
"n": "Device Battery",
"t": "custom",

Etc

It might be your api is sending that but if what you post in this forun is not proper json then we have a problem helping you

7

Hello Yosiasz,

It is difficult to explain full JSON but let me put it very simple way so you can help.

QUERY-1 Result:
{
“1”: 100,
“2”: 78.3448275857,
“3”: 214,
“4”: 32,
“5”: 1,
“6”: 23.117,
“7”: 28807.694,
“8”: -348201.3876
}

QUERY-2 Result:
{
“1”: “Device Battery”,
“2”: “Fuel Level”,
“3”: “Fuel Tank Capacity”,
“4”: “Fuel Tank Temperature”,
“5”: “Ignition”,
“6”: “Main Power”,
“7”: “Odometer”,
“8”: "Mileage "
}