Convert JSON from Gitlab to CSV with JQ

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?

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: https://stackoverflow.com/questions/32960857/how-to-convert-arbitrary-simple-json-to-csv-using-jq