How to use jq to convert 2 objects into CSV?

I'm trying to convert objects that look like this:

{
  "metricId": "metric1",
  "data": [
    {
      "dimensions": [
        "DEVICE-a1b2c3",
        "queue1"
      ],
      "dimensionMap": {
        "Queue": "queue1",
        "enitity": "DEVICE-a1b2c3"
      },
      "timestamps": [
        1626286800000
      ],
      "values": [
        1
      ]
    },
    {
      "dimensions": [
        "DEVICE-a1b2c3",
        "queue2"
      ],
      "dimensionMap": {
        "Queue": "queue2",
        "entity": "DEVICE-a1b2c3"
      },
      "timestamps": [
        1626286800000
      ],
      "values": [
        2
      ]
    }
  ]
}
{
  "metricId": "metric2",
  "data": [
    {
      "dimensions": [
        "DEVICE-a1b2c3",
        "queue1"
      ],
      "dimensionMap": {
        "Queue": "queue1",
        "entity": "DEVICE-a1b2c3"
      },
      "timestamps": [
        1626286800000
      ],
      "values": [
        11
      ]
    },
    {
      "dimensions": [
        "DEVICE-a1b2c3",
        "queue2"
      ],
      "dimensionMap": {
        "Queue": "queue2",
        "entity": "DEVICE-a1b2c3"
      },
      "timestamps": [
        1626286800000
      ],
      "values": [
        22
      ]
    }
  ]
}

To CSV that looks like this:

"metric1","queue1",1626286800000,1
"metric1","queue1",1626286800000,2
"metric2","queue1",1626286800000,11
"metric2","queue1",1626286800000,22

I was somewhat successful but I'm getting duplicates in my results.

Command: jq -r '. | {id:.metricId, queue: .data[].dimensionMap.Queue, time: .data[].timestamps[0], value: .data[].values[0]} | [.id, .queue, .time, .value] | @csv'

Output:

"metric1","queue1",1626286800000,1
"metric1","queue1",1626286800000,2
"metric1","queue1",1626286800000,1
"metric1","queue1",1626286800000,2
"metric1","queue2",1626286800000,1
"metric1","queue2",1626286800000,2
"metric1","queue2",1626286800000,1
"metric1","queue2",1626286800000,2
"metric2","queue1",1626286800000,11
"metric2","queue1",1626286800000,22
"metric2","queue1",1626286800000,11
"metric2","queue1",1626286800000,22
"metric2","queue2",1626286800000,11
"metric2","queue2",1626286800000,22
"metric2","queue2",1626286800000,11
"metric2","queue2",1626286800000,2

I've looked over the documentation and several blog posts/videos but I haven't been able to find a solution so far. Thank you for your help.

Solution

One way to tackle the problem is to use jq "$-variables":

.metricId as $metricId
| .data[]
| .dimensionMap.Queue as $q
| [.timestamps, .values] | transpose[]
| [$metricId, $q, .[]]
| @csv