Convert JSON from Gitlab to CSV with JQ

Question

I am currently trying to create a json convert job in gitlab and I am running into issues with jq.

The following is the output from gitlab after doing container scanning and I am trying to convert it to a csv by using jq to get the information I want.

This is so I can include a readable csv as a build artifact.

"vulnerabilities": [
    {
      "id": "CVE-2015-5186",
      "category": "container_scanning",
      "message": "Audit: log terminal emulator escape sequences handling",
      "description": "Audit before 2.4.4 in Linux does not sanitize escape characters in filenames.",
      "cve": "CVE-2015-5186",
      "severity": "Medium",
      "solution": "No solution provided",
      "scanner": {
        "id": "trivy",
        "name": "trivy"
      },
      "location": {
        "dependency": {
          "package": {
            "name": "audit-libs"
          },
          "version": "2.8.5-4.el7"
        },
        "operating_system": "Unknown",
        "image": "image.tar"
      },
      "identifiers": [
        {
          "type": "cve",
          "name": "CVE-2015-5186",
          "value": "CVE-2015-5186",
          "url": "example.com"
        }
      ],
      "links": [{
          "url": "example.com"
        },{
          "url": "example.com"
        },{
          "url": "example.com"
        },{
          "url": "example.com"
        },{
          "url": "example.com"
        },{
          "url": "example.com"
        },{
          "url": "example.com"
        },{
          "url": "example.com"
        },{
          "url": "example.com"
        }
      ]
    }

The following is my current jq statement and I am not sure what I am missing to make this work.

- echo "id, message, description, severity, name, version, identifiers" > $CI_PROJECT_DIR/reports/${IMAGENAME}-trivy.csv

- jq -r '.vulnerabilities | map([.id, .message, .description, .severity, (.location | .dependency, .package, .name), (.location | .dependency, .version), (.identifiers | .0, .type), (.identifiers | .0, .name), (.identifiers | .0, .value), (.identifiers | .0, .url)] | join(", ")) | join("\n")' $CI_PROJECT_DIR/reports/gl-container-scanning-report.json >> $CI_PROJECT_DIR/reports/${IMAGENAME}-trivy.csv

which results in

$ jq -r '.vulnerabilities | map([.id, .message, .description, .severity, (.location | .dependency, .package, .name), (.location | .dependency, .version), (.identifiers | .0, .type), (.identifiers | .0, .name), (.identifiers | .0, .value), (.identifiers | .0, .url)] | join(", ")) | join("\n")' $CI_PROJECT_DIR/reports/gl-container-scanning-report.json >> $CI_PROJECT_DIR/reports/${IMAGENAME}-trivy.csv
jq: error (at /builds/group/subgroup/project/reports/gl-container-scanning-report.json:34424): Cannot index array with string "type"

I have also tried slightly modifying to remove the identifiers which resulted in this.

$ echo "id, message, description, severity, name, version" > $CI_PROJECT_DIR/reports/${IMAGENAME}-trivy.csv
$ jq -r '.vulnerabilities | map([.id, .message, .description, .severity, (.location | .dependency, .package, .name), (.location | .dependency, .version)] | join(", ")) | join("\n")' $CI_PROJECT_DIR/reports/gl-container-scanning-report.json >> $CI_PROJECT_DIR/reports/${IMAGENAME}-trivy.csv
jq: error (at /builds/jdiss/fdtm/galelisten/reports/gl-container-scanning-report.json:34424): string ("CVE-2015-5...) and object ({"package":...) cannot be added

I am trying to figure out the location stuff so I can get the package name and its version.

I am also trying to get the identifiers information.

Any idea what I might be missing?

Is this [jqplay.org example](https://jqplay.org/s/0hqrrNM1cSj) or [this example](https://jqplay.org/s/pjdMrMoxHIT) close to what you want? — rickhg12hs, May 13 '23 at 03:06
It's unclear why you add multiple zeros to your output (`.0` is just `0` (`0.0`)) or why you access fields that don't exist at that level — knittl, May 13 '23 at 06:22

knittl · Answer 1 · 2023-05-13T09:05:22.783

You seem to be confused about the usage of . (object identifier-index), , (comma), and | (pipe).

If you want to access the package of the following JSON object:

{
  "location": {
    "dependency": {
      "package": {
        "name": "audit-libs"
      },
      "version": "2.8.5-4.el7"
    },
    "operating_system": "Unknown",
    "image": "image.tar"
  }
}

the syntax is .location | .dependency | .package or .location.dependency.package (but not .location, .dependency, .package; the latter is a stream of 3 properties all from the same level in your JSON).

Elements of an array are accessed with [0] and not with .0 (.0 is the decimal number 0.0).

Now, if you want CSV, it is easier to use the builtin @csv filter. You don't need that echo for the headers either, jq can add the headers just fine:

["id", "message", "description", "severity", "name", "version", "identifiers"],
(
    .vulnerabilities[]
    | [
      .id,
      .message,
      .description,
      .severity,
      (.location.dependency | .package.name, .version),
      (.identifiers[0] | .type, .name, .value, .url)
    ]
) | @csv

(Raw) output:

"id","message","description","severity","name","version","identifiers"
"CVE-2015-5186","Audit: log terminal emulator escape sequences handling","Audit before 2.4.4 in Linux does not sanitize escape characters in filenames.","Medium","audit-libs","2.8.5-4.el7","cve","CVE-2015-5186","CVE-2015-5186","example.com"

Further reading: How to convert arbitrary simple JSON to CSV using jq?

Convert JSON from Gitlab to CSV with JQ

1 Answers1