How can I reference first query results in the second query in a Grafana panel?

I have a panel in Grafana that displays IDs and their respective counts. However, I would like to resolve and replace these IDs with their corresponding names, which are stored in a different database. What is the best way to achieve this in Grafana?

The Panel looks like this

This is queried from clickhouse db, the rest of the data I want is stored in postgresql.

For now I created an additional template variable with this query and have another panel which displays data from postgres. But this isn't ideal and I would rather it displayed in the same panel somehow.

I looked into merge and joining of datasources but it seems like I would need to query the entire user table when I just want to resolve IDs of 15 users.

Solution

You can join results of two queries using Grafana's transformation.

Generally, you can't use results of one query in another query.

But in this exact case you could use a little trick: create hidden variable based on query to one data source, and then use this variable to query another data source.

For your case you'll need to follow below algorithm.

Prepare dashboard variable

Goto: Dashboard stettings > Variables > New variable:

Select variable type: Query,
Name: not important. In this example I will use query0,
Show on dashboard: Nothing,
Data source: Your clickhouse data source,
Query: query from existing panel with names only name concatenated by comma. Something like this:

-- I'm not familiar with clickhouse, and I don't have access to some public demo, so query should be double-checked.
SELECT groupArray(userid) FROM myTable
-- result should be something like:
-- ['123123-213-123-123232','123213-333-333-333122']

Regex: .(.+). (to drop surrounding square brackets),
Run query.

In Preview of values you should see one string of format '123123-213-123-123232','123213-333-333-333122'

Create filtered query and join it

Go to panel with current data.

Change Data source to -- Mixed --,
+ Query
select Data source Postgres,
switch from Builder to Code (top right corner),
right select for your user info. Something along the lines:

select userId, userName from my_users t where t.userId in ($query0)

Run query. In the bottom should appear selector of time series,
switch to tab Transform,
select Join by field. Field: userId.

Here I assume column containing guid of user named userId in both databases. Correct to suite your situation.

Caution, if column containing user ids in databases differs, you'll need to use alias to make it the same: AFAIK, Grafana can join on fields with different names.