JSON to table formatting (got it from - http://json2table.com)

Question

I am currently trying to integrate AWS ECR Scanning to our CI/CD pipeline and pass the results to our engineers in a human readable form.

The command - aws ecr describe-image-scan-findings --repository-name ${REPNAME} --image-id imageTag=latest --profile ${PROFILE} --region ${REGION}

Returns something like the following [redacted] output -

    "imageScanFindings": {
        "findings": [
            {
                "name": "CVE-2018-12886",
                "description": "stack_protect_prologue in cfgexpand.c and stack_protect_epilogue in function.c in GNU Compiler Collection (GCC) 4.1 through 8 (under certain circumstances) generate instruction sequences when targeting ARM targets that spill the address of the stack protector guard, which allows an attacker to bypass the protection of -fstack-protector, -fstack-protector-all, -fstack-protector-strong, and -fstack-protector-explicit against stack overflow by controlling what the stack canary is compared against.",
                "uri": "https://security-tracker.debian.org/tracker/CVE-2018-12886",
                "severity": "MEDIUM",
                "attributes": [
                    {
                        "key": "package_version",
                        "value": "8.3.0-6"
                    },
                    {
                        "key": "package_name",
                        "value": "gcc-8"
                    },
                    {
                        "key": "CVSS2_VECTOR",
                        "value": "AV:N/AC:M/Au:N/C:P/I:P/A:P"
                    },
                    {
                        "key": "CVSS2_SCORE",
                        "value": "6.8"
                    }
                ]
            },
            {
                "name": "CVE-2020-1751",
                "description": "An out-of-bounds write vulnerability was found in glibc before 2.31 when handling signal trampolines on PowerPC. Specifically, the backtrace function did not properly check the array bounds when storing the frame address, resulting in a denial of service or potential code execution. The highest threat from this vulnerability is to system availability.",
                "uri": "https://security-tracker.debian.org/tracker/CVE-2020-1751",
                "severity": "MEDIUM",
                "attributes": [
                    {
                        "key": "package_version",
                        "value": "2.28-10"
                    },
                    {
                        "key": "package_name",
                        "value": "glibc"
                    },
                    {
                        "key": "CVSS2_VECTOR",
                        "value": "AV:L/AC:M/Au:N/C:P/I:P/A:C"
                    },
                    {
                        "key": "CVSS2_SCORE",
                        "value": "5.9"
                    }
                ]
            },
            {
                "name": "CVE-2019-20367",
                "description": "nlist.c in libbsd before 0.10.0 has an out-of-bounds read during a comparison for a symbol name from the string table (strtab).",
                "uri": "https://security-tracker.debian.org/tracker/CVE-2019-20367",
                "severity": "MEDIUM",
                "attributes": [
                    {
                        "key": "package_version",
                        "value": "0.9.1-2"
                    },
                    {
                        "key": "package_name",
                        "value": "libbsd"
                    },
                    {
                        "key": "CVSS2_VECTOR",
                        "value": "AV:N/AC:L/Au:N/C:P/I:N/A:P"
                    },
                    {
                        "key": "CVSS2_SCORE",
                        "value": "6.4"
                    }
                ]
            },
            {
                "name": "CVE-2019-12904",
                "description": "In Libgcrypt 1.8.4, the C implementation of AES is vulnerable to a flush-and-reload side-channel attack because physical addresses are available to other processes. (The C implementation is used on platforms where an assembly-language implementation is unavailable.)",
                "uri": "https://security-tracker.debian.org/tracker/CVE-2019-12904",
                "severity": "MEDIUM",
                "attributes": [
                    {
                        "key": "package_version",
                        "value": "1.8.4-5"
                    },
                    {
                        "key": "package_name",
                        "value": "libgcrypt20"
                    },
                    {
                        "key": "CVSS2_VECTOR",
                        "value": "AV:N/AC:M/Au:N/C:P/I:N/A:N"
                    },
                    {
                        "key": "CVSS2_SCORE",
                        "value": "4.3"
                    }
                ]
            }
        ],
        "imageScanCompletedAt": "2020-10-23T00:03:10+05:30",
        "vulnerabilitySourceUpdatedAt": "2020-10-22T16:21:44+05:30",
        "findingSeverityCounts": {
            "MEDIUM": 14,
            "INFORMATIONAL": 72,
            "LOW": 18,
            "UNDEFINED": 3
        }
    },
    "registryId": "12345678911",
    "repositoryName": "name-of-repo",
    "imageId": {
        "imageDigest": "sha256:1213412412412451241414214141412412",
        "imageTag": "latest"
    },
    "imageScanStatus": {
        "status": "COMPLETE",
        "description": "The scan was completed successfully."
    }
}

The above is not human friendly to read, especially if there are a lot of findings and the JSON output runs into hundreds of lines.

I want to convert the above output in a more "human" readable form without omitting any of the returned info. I tried using the --output table option for AWS CLI but it is including a lot of spaces in between the columns and rows.

I tried using jq to convert it into a table or .tsv of some sort using map, but with no luck as I am a total beginner in JQ. If someone has any ideas on how to approach this - any help would be appreciated.

Aiming to get something along the following lines which I got from http://json2table.com/ -

Expected Formatted Output

Answer 1

The following produces a table with nested subtables in accorance with my understanding of the basic requirements.

Example

To clarify what the main function json2tree does, here is an example:

Input:

[
  {"a": 1,"b": 2,"ary":[{"c":0,"d":1},{"c":10,"d":11}],"another":[{"a":1,"b":2}]},
  {"a":10,"b":20,"c":30,"ary":[{"e":0,"f":1},{"g":10,"h":11}],"xyzzy":"x"}
 ] 

Output (untabified):

a       b       ary                             another         c       xyzzy
1       2       c       d                       a       b       null    null
                0       1                       1       2               
                10      11                                              
10      20      e       f       g       h                       30      x
                0       1       null    null                            
                null    null    10      11                              
            

Assumptions

Bugs aside, the only assumptions regarding the input data are as follows:

1. The input is an array of JSON objects, $a;
2. If $a[$i][$k] is an array of JSON objects for some $i and some key $k, then $a[$j][$k] is an array of JSON objects for all $j

Robustness

The complexity (or at least length) of the implementation is the result of its being fairly robust with respect to the input data. For example, there is no requirement that the top-level objects have the same keys, or that they be specified in a consistent order.

This robustness, however, is achieved at the cost that tostring is called whenever a non-scalar JSON entity is found in a context that is not envisioned by (2) above.

Helper Functions

# determine if the input is an array of JSON objects
def is_array_of_objects: type=="array" and all(.[]; type=="object");

# input: a JSON object
def reorder_keys($object): . as $in | $object | with_entries(.value = $in[.key]);

# input: an array of one or more objects
# output: an array of similar objects but altered by adding null values so that they all have the same keys,
#         and so that the keys are in the same order
def synthesize:
  (add|map_values(null)) as $a
  | map($a + .)
  | .[0] as $template
  | map(reorder_keys($template));

# Input: an array of conformal objects, i.e., with the same keys,
#        and such that if .[$i][$k] is an array of objects for any $i or $i, then .[$j][$k] is 
#        also an array of objects for all $j
# Output: an object with the same keys but with the value replaced by the max `length` for an array of objects,
#        and -1 otherwise.
def object_of_widths:
  def w: map_values(if is_array_of_objects then .[0]|length else -1 end);
  def keywise_maximum($x; $y): reduce ($y|keys_unsorted)[] as $k ({}; .[$k] = ([$x[$k],$y[$k]]|max));
  reduce (.[]|w) as $w ({}; keywise_maximum(.; $w));

Main program

# Input: an array of objects
# Output: a table that treats top-level arrays of JSON objects specially
def json2tree:

  # input: an array
  # output: an array of length at least $n
  def rpad($n): . + [range(0;$n-length)|null];
  
  # input: an object
  def print_first_line($keys; $widths):
    [$keys[] as $k | $k, (if $widths[$k] == -1 then empty else range(1;$widths[$k]) | null end)];
    
  # input: an object
  def print_second_line($keys; $widths):
    [$keys[] as $k
    | if $widths[$k] == -1
      then .[$k]|tostring
      elif .[$k] then .[$k][0]|keys_unsorted|rpad($widths[$k])[]
      else range(0; $widths[$k]) | null
      end];

  # input: an object
  # $i - the $i-th object in the array
  def print_nth_line($i; $keys; $widths):
    [$keys[] as $k
    | if $widths[$k] == -1
      then null
      elif $i < (.[$k]|length)
      then (.[$k][$i] | map(tostring) | rpad($widths[$k]))[]
      else (range(0;$widths[$k])|null)
      end] ;

  synthesize
  | map(map_values(if is_array_of_objects then synthesize else . end))
  | (.[0] | keys_unsorted) as $keys
  | object_of_widths as $widths
  | (.[0] | print_first_line($keys; $widths)),
    (range(0; length) as $ix
     | (.[$ix] | print_second_line($keys; $widths)),
       (.[$ix]
        | ([.[] | if is_array_of_objects then length else 0 end] | max) as $mx
        | range(0; $mx) as $i | print_nth_line($i; $keys; $widths) ) ) ;

json2tree | @tsv