Nested JSON with variable keys to TSV using jq

Question

I have the following nested JSON file labs.json with variable keywords (lab001, lab002, etc.) which I would like to convert into a TSV using jq:

{
  "lab001": {
    "tags": {
      "T1": [],
      "T2": ["k26","e23"],
      "T3": ["s92"]
    },
    "code": "8231"
  },
  "lab002": {
    "tags": {
      "T1": ["t32","y55"],
      "T2": ["q78"],
      "T3": ["b24"]
    },
    "code": "9112"
  }
}

The resulting table should look like:

ID	T1	T2	T3
lab001		k26,e23	s92
lab002	t32,y55	q78	b24

Currently I am using a rather pedestrian approach by pasting two calls of jq and doing some cleanup with tr:

paste <(jq -r 'keys_unsorted | @csv' labs.json | tr ',' '\n') <(jq -r '.[].tags | map(tostring) | @tsv' labs.json) | tr -d '[]"'

Is there any more elegant way to get this done purely with jq?

Answer 1

Join elements of each tag by commas, put resulting strings into an array with the lab ID as the first element, and pipe it to the @tsv filter like so:

keys_unsorted[] as $id | [$id, (.[$id].tags[] | join(","))] | @tsv

^{Online demo}