Hi, I am new to Grafana.
I have Grafana v11.0.0, marcusolsson-json-datasource v1.3.3.
I get a list of payments from the API. I display the data grouped by the address where the payment took place and the organization that made the payment. I also need to display the type of payment for each address and organization. Each payment received from the API contains a “type” field, which is the payment ID. I need to display the type name on the panel, not the payment ID. I have a database in which there is a payment_types table, which just contains the names of the payment types I need. As I understand it, I need to add a new datasource to my database and somehow match the IDs obtained from the API with the names from the database and display them on the panel. But I do not know how, so I will be glad if someone helps me. Thank you.
welcome to forum @fooniquler
how comfortable are you working with jsonata querying?
I’ve never used it, but after googling, I saw the syntax and I think there will be no problems using jsonata
i like your style, you like to tinker, very good.
so I would try this.
- i personally like to use infinity plugin for api stuff
- create a dashboard variable that gets you the list of payment_types
- in jsonata pass in this variable, then do the matching by payment_id to get the name
something odd though, did you say payment_types only has name column?
Do you mean to create a variable with a query like “SELECT id, name FROM payment_types” and then apply the filter function in jsonata? And of course no, the table payment_types not only has the column name, but for this problem that’s not important)
yes like that.but make it hidden
also its indeed important that it provides 2 columns one to filter that payload by id and the 2nd to get payment name
I added a variable, but I don’t understand how to get the names correctly. I tried different variations of using the filter function, but nothing worked, but I noticed that apparently the response from the database comes in the wrong format.
The attached image shows an example for a test that did not return anything to me, although there is definitely such an ID in the database. I also tried to change the format of the data returned from the database: SELECT json_agg(json_build_object(‘id’, id, ‘name’, name)) FROM payment_types and then try to filter, but this was also unsuccessful.
what does $pt look like in jsonata if you just do something like this not sure of exact syntax
(jsonata $pt)
It just displays the name with id 1
Updated: now it displays id 1, not the name, although I didn’t change anything
For some reason, only the first value is always displayed. I tried to create a variable that filters the returned list by time range.
Preview of values:
and when I try to display this variable via jsonata, I get only the first element with the id 4648 from the example
So, any ideas?
I am stuck with this one so posted on github repo
what does your variable look like and how you are using it in jsonata ? @yesoreyeram any idea?
Yep
Preview of values:
The result is the same as in the preview of values:
If I do something like “$pt[*].id”, I get an empty result
nice. now that you have pt jsonized, what you want to do is marry this pt variable with the result of your rest api query. not query pt but iterate on the result of your api and check $pt to find name. never done this so stumbling in the dark
And how do I check $pt to find the name, if I access the variable field, I get an empty response, like ($pt.name) won’t show me anything? Also, I looked and found something strange: $pt is an array that consists of a single element, which is a string, apparently, db data conversion does not work as intended
like I said never done this before so we will be looking to you to provide us guidance 
So, I have found a solution. I have a variable $pt:
This variable is an array consisting of a single string - jsons, separated by a comma:
So, first of all we need to convert this string to an array, we can achieve that by using $eval function:
$eval($pt[0])Then using the combination of map and filter functions we can get the names of payment types from db by ids from api
