How to use merge transformation

Grafana Dashboards
1

I think I’m using Merge wrong or at least I’m very confused by it!

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

Ubuntu with Grafana in container. It’s the latest image.

  • What are you trying to achieve?

I have four tables. Let’s say they’re called A (id of B and C), B (id, name, age), C (colour, make) and D. A is basically a many-to-many lookup table so that B and C both have an ID field in it. Let’s also say that B has a many-to-one relationship with D.

I ensure that the name of the id fields matched so that B ID was called PersonId and C ID was called CarId. I made sure the id field in B and C reflected this too.

I’m trying to merge those tables into a single view like you’d be able to do with SQL. Unfortunately, I’m using the JSON Infinity Plugin which pulls from four different APIs.

  • How are you trying to achieve it?

I’ve been trying to use the Merge transform. This seems to work to join two of the queries together but not all.

I’ve also tried using the Config from Query transform but that’s a bit messy if there’s multiple columns in each table.

  • What happened?

The tables did not merge. I hid two of the tables and then it successfully joined. Unhiding them made it not work again.

  • What did you expect to happen?

The tables to join.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No I didn’t have any errors.

1 Like
2

welcome @thescienceguykris

Can you please answer these questions

  1. what is your datasource? mysql, ms sql?
  2. what does the table schema look on these tables
3

Hey, thank you for responding!

It’s using the Infinity plugin which pulls JSON data from an API.

A representative schema is below:

IMG_3166
IMG_31661170×519 92.4 KB

4

@yosiasz does that help?

I can sometimes get either A and B or A and C to merge (when I hide the other table) but can’t ever get it all to merge.

5

I think you need a common column in each table.

1 Like
6

It’s possible to join using a transformation provided that the two data frames have a field in common. There’s a blog about this with screenshots showing you how:

Broadly, “merge” just glues two data frames together and is best when the two data frames have exactly the same schema and you just want to “add more records to the bottom of the result set”. Join by ID is much more like a SQL join.

Note that while Grafana can do a number of SQL like operations, if you can do it in the underlying database you are always going to be better off doing it there, that is, write a more complex SQL query and return one frame.

1 Like
7

You can “concatenate fields” and then organize the fields by name.
And hide the redundant ones.

8

Hey, thank you I’ve seen this page before. It doesn’t really solve my issue though because like in my example I want to glue multiple tables together on different variables. Is there a way to do multiple joins?

9

@joachimschiewek does this actually merge the data correctly though? The rows are supposed to match each other

@davidallen5 is multiple joins already something that is possible? I wouldn’t mind doing some work to make it if not.

10

Multiple joins should already be possible. Instead of 2 queries and one join transform, you progressively join more queries. What have you tried?

11

Hey @davidallen5 it’s not possible. When I put one join in, it effectively destroys the other frames. Did think this was odd though so I’m guessing it isn’t the desired behaviour?

12

please post sample data as json, also what visualization will you be using for this?

like

[{
  "relationshipId": 1,
  "userId": 1,
  "carId": 1,
  "broughtDate": "11/30/2024",
  "soldDate": "9/4/2025"
}, {
  "relationshipId": 2,
  "userId": 2,
  "carId": 2,
  "broughtDate": "6/1/2024",
  "soldDate": "9/27/2025"
}, {
  "relationshipId": 3,
  "userId": 3,
  "carId": 3,
  "broughtDate": "7/14/2024",
  "soldDate": "10/16/2025"
}, {
  "relationshipId": 4,
  "userId": 4,
  "carId": 4,
  "broughtDate": "3/30/2024",
  "soldDate": "8/7/2025"
}]

etc

13

Like @yosiasz says, please post a concrete example.

Let me clarify, I might’ve answered too fast and missed some stuff earlier.

If you use MERGE specifically, yes it destroys the original data frames (really, it transforms them in a pipeline).

3 queries

image
image1274×1490 51.8 KB

1 transform

image
image1148×596 24.3 KB

1 merge

image
image1816×858 22.9 KB

The way to think about this is as a pipeline. If you had 10 different queries with 10 different data formats, what I’d advise you to do is:

  1. Create a set of transformations which iteratively re-shaped each of the queries into the same schema
  2. Filter out whatever records you don’t want
  3. Merge as the last/final step
14

Yeah no problem!

Table A - Car Ownership

[
  {
    "id": 1,
    "userid": 1,
    "carid": 2,
    "boughtDate": "2023-01-01",
    "soldDate": null
  },
  {
    "id": 2,
    "userid": 2,
    "carid": 1,
    "boughtDate": "2023-03-15",
    "soldDate": "2024-01-20"
  },
  {
    "id": 3,
    "userid": 2,
    "carid": 2,
    "boughtDate": "2022-07-10",
    "soldDate": null
  }
]

Table B - Customers

[
    {
      "userid": 1,
      "firstname": "John",
      "lastname": "Doe",
      "email": "john.doe@example.com"
    },
    {
      "userid": 2,
      "firstname": "Jane",
      "lastname": "Smith",
      "email": "jane.smith@example.com"
    }
]

Table C - Cars

[{
      "id": 1,
      "make": "Toyota",
      "model": "Corolla",
      "colour": "Red",
      "manufacturer_id": 1
    },
    {
      "id": 2,
      "make": "Honda",
      "model": "Civic",
      "colour": "Blue",
      "manufacturer_id": 2
}]

Table D - Manafacturers

 [
    {
      "id": 1,
      "name": "Toyota Motors",
      "phone": "123-456-7890"
    },
    {
      "id": 2,
      "name": "Honda Corporation",
      "phone": "987-654-3210"
    },
    {
      "id": 3,
      "name": "Ford Motor Company",
      "phone": "555-123-4567"
    }
  ]
1 Like
15

I don’t think I’m trying to use the merge transform after all - it looks like I’m trying to use multiple joins, but it doesn’t seem possible.

16

and which visualization you want to use ?

18

Just a simple table

19

best I could come up with is using business text and joining the data using javascript and using plotly for table

image
image1740×560 73.4 KB