147

Just started out with Bash scripting and stumbled upon jq to work with JSON.

I need to transform a JSON string like below to a table for output in the terminal.

[{
    "name": "George",
    "id": 12,
    "email": "george@domain.example"
}, {
    "name": "Jack",
    "id": 18,
    "email": "jack@domain.example"
}, {
    "name": "Joe",
    "id": 19,
    "email": "joe@domain.example"
}]

What I want to display in the terminal:

ID        Name
=================
12        George
18        Jack
19        Joe

Notice how I don't want to display the email property for each row, so the jq command should involve some filtering. The following gives me a plain list of names and id's:

list=$(echo "$data" | jq -r '.[] | .name, .id')
printf "$list"

The problem with that is, I cannot display it like a table. I know jq has some formatting options, but not nearly as good as the options I have when using printf. I think I want to get these values in an array which I can then loop through myself to do the formatting...? The things I tried give me varying results, but never what I really want.

Can someone point me in the right direction?

Stephen Ostermiller
  • 23,933
  • 14
  • 88
  • 109
Rein
  • 3,211
  • 3
  • 16
  • 21

8 Answers8

175

Using the @tsv filter has much to recommend it, mainly because it handles numerous "edge cases" in a standard way:

.[] | [.id, .name] | @tsv

Adding the headers can be done like so:

jq -r '["ID","NAME"], ["--","------"], (.[] | [.id, .name]) | @tsv'

The result:

ID  NAME
--  ------
12  George
18  Jack
19  Joe

As pointed out by @Tobia, you might want to format the table for viewing by using column to post-process the result produced by jq. If you are using a bash-like shell then column -ts $'\t' should be quite portable.

length*"-"

To automate the production of the line of dashes:

jq -r '(["ID","NAME"] | (., map(length*"-"))), (.[] | [.id, .name]) | @tsv'
peak
  • 105,803
  • 17
  • 152
  • 177
  • 2
    The @tsv filter is even on the basic filters manual page for jq, hmm... I wonder what else I might have missed :) – Ярослав Рахматуллин Feb 24 '19 at 16:55
  • 1
    I would like to add a standard Linux utility `column` that you can use to format TSV into pretty lists. You can use it like this: `jq -r '... | @tsv' | column -nts$'\t'` or you can save it as an alias in .bashrc `alias tsvtable="column -nts\$'\\t'"` and use it like `... | tsvtable` – Tobia Aug 01 '22 at 13:54
112

Why not something like:

echo '[{
    "name": "George",
    "id": 12,
    "email": "george@domain.example"
}, {
    "name": "Jack",
    "id": 18,
    "email": "jack@domain.example"
}, {
    "name": "Joe",
    "id": 19,
    "email": "joe@domain.example"
}]' | jq -r '.[] | "\(.id)\t\(.name)"'

Output

12  George
18  Jack
19  Joe

Edit 1 : For fine grained formatting use tools like awk

 echo '[{
    "name": "George",
    "id": 12,
    "email": "george@domain.example"
}, {
    "name": "Jack",
    "id": 18,
    "email": "jack@domain.example"
}, {
    "name": "Joe",
    "id": 19,
    "email": "joe@domain.example"
}]' | jq -r '.[] | [.id, .name] | @csv' | awk -v FS="," 'BEGIN{print "ID\tName";print "============"}{printf "%s\t%s%s",$1,$2,ORS}'
ID  Name
============
12  "George"
18  "Jack"
19  "Joe"

Edit 2 : In reply to

There's no way I can get a variable containing an array straight from jq?

Why not?

A bit involved example( in fact modified from yours ) where email is changed to an array demonstrates this

echo '[{
    "name": "George",
    "id": 20,
    "email": [ "george@domain1.example" , "george@domain2.example" ]
}, {
    "name": "Jack",
    "id": 18,
    "email": [ "jack@domain3.example" , "jack@domain5.example" ]
}, {
    "name": "Joe",
    "id": 19,
    "email": [ "joe@domain.example" ]
}]' | jq -r '.[] | .email'

Output

[
  "george@domain1.example",
  "george@domain2.example"
]
[
  "jack@domain3.example",
  "jack@domain5.example"
]
[
  "joe@domain.example"
]
Stephen Ostermiller
  • 23,933
  • 14
  • 88
  • 109
sjsam
  • 21,411
  • 5
  • 55
  • 102
  • Thank you for your answer. This works very good in this particular case, the id's have all the same length. Imagine I'd switch the order the fields, that would give me something that doesn't look like a handy table at all. I really am looking for a solution I could use on more datasets. Thanks for your answer, though! – Rein Aug 25 '16 at 07:55
  • Ok, got it. There's no way I can get a variable containing an array straight from jq? I always have to go from a string? – Rein Aug 25 '16 at 08:03
  • Thanks for your help, the output is exactly as I wanted (except for the quotes around the names). It felt strange to go from a string instead of getting a ready to use array like we would in for example Python. To me it feels clumsy and dirt, but I guess it's just me that has to get used to the ideas of bash? I'll try to make this into a function I can reuse, so I can use this for more JSON strings with different headers. – Rein Aug 25 '16 at 08:54
  • @Rein : for fine-grained formatting you need print the output in the csv format and then use `awk`, but mind that complicated cases may fail. For your second comment see the last edit and read it together with [\[ this \]](http://stackoverflow.com/a/18238241/1620779) answer. – sjsam Aug 25 '16 at 08:58
  • The `jq` array should not be confused with [\[ bash arrays \]](http://tldp.org/LDP/Bash-Beginners-Guide/html/sect_10_02.html) which is an entirely different thing. In fact what you might need is bash [\[ wrapper \]](http://tldp.org/LDP/abs/html/wrapper.html) over jq. But I'm doubtful it will be any good except for a few test cases considering the myriad of jsons that you've to parse. – sjsam Aug 25 '16 at 09:02
  • Thank you for diving deep into my question. Actually the question is answered already, but I'd still like to figure this out a bit more. After your edit it seems possible to get arrays from jq, for example for "email", but it's not possible to get associative arrays from jq, right? Ideally I would like to get a bash array from it, representing the JSON string. Just like you would do in any other language eg: `myarray = json_encode(myjsonstring)`. If this isn't possible, how exactly do people use jq then? I imagine they want to loop over the data at some point? – Rein Aug 25 '16 at 09:12
  • @Rein If I'm not mistaken, there is no associative array in json as far as I could infer from this [\[ page \]](http://json.org/). Or I may be wrong with the terminology. :\ . A `json object` resembles a `bash` associative array though. In fact [\[ this \]](http://xmeblog.blogspot.in/2015/02/transforming-json-object-into.html) post shows how to convert an json array to bash array. What is demonstrated there are customized bash wrappers to achieve certain objectives.. – sjsam Aug 25 '16 at 09:34
43

Defining headers by hand is suboptimal! Omitting headers is also suboptimal.

TL;DR

data

[{ "name": "George", "id": 12, "email": "george@domain.example" },
{ "name": "Jack", "id": 18, "email": "jack@domain.example" },
{ "name": "Joe", "id": 19, "email": "joe@domain.example" }]

script

  [.[]| with_entries( .key |= ascii_downcase ) ]
      |    (.[0] |keys_unsorted | @tsv)
         , (.[]  |map(.) |@tsv)

how to run

$ < data jq -rf script  | column -t
name    id  email
George  12  george@domain.example
Jack    18  jack@domain.example
Joe     19  joe@domain.example

I found this question while summarizng some data from amazon web services. The problem I was working on, in case you want another example:

$ aws ec2 describe-spot-instance-requests | tee /tmp/ins |
    jq  --raw-output '
                                     # extract instances as a flat list.
    [.SpotInstanceRequests | .[]
                                     # remove unwanted data
    | {
        State,
        statusCode: .Status.Code,
        type: .LaunchSpecification.InstanceType,
        blockPrice: .ActualBlockHourlyPrice,
        created: .CreateTime,
        SpotInstanceRequestId}
    ]
                                        # lowercase keys
                                        # (for predictable sorting, optional)
    |  [.[]| with_entries( .key |= ascii_downcase ) ]
        |    (.[0] |keys_unsorted | @tsv)               # print headers
           , (.[]|.|map(.) |@tsv)                       # print table
    ' | column -t

Output:

state      statuscode                   type     blockprice  created                   spotinstancerequestid
closed     instance-terminated-by-user  t3.nano  0.002000    2019-02-24T15:21:36.000Z  sir-r5bh7skq
cancelled  bad-parameters               t3.nano  0.002000    2019-02-24T14:51:47.000Z  sir-1k9s5h3m
closed     instance-terminated-by-user  t3.nano  0.002000    2019-02-24T14:55:26.000Z  sir-43x16b6n
cancelled  bad-parameters               t3.nano  0.002000    2019-02-24T14:29:23.000Z  sir-2jsh5brn
active     fulfilled                    t3.nano  0.002000    2019-02-24T15:37:26.000Z  sir-z1e9591m
cancelled  bad-parameters               t3.nano  0.002000    2019-02-24T14:33:42.000Z  sir-n7c15y5p

Input:

$ cat /tmp/ins
{
    "SpotInstanceRequests": [
        {
            "Status": {
                "Message": "2019-02-24T15:29:38+0000 : 2019-02-24T15:29:38+0000 : Spot Instance terminated due to user-initiated termination.",
                "Code": "instance-terminated-by-user",
                "UpdateTime": "2019-02-24T15:31:03.000Z"
            },
            "ActualBlockHourlyPrice": "0.002000",
            "ValidUntil": "2019-03-03T15:21:36.000Z",
            "InstanceInterruptionBehavior": "terminate",
            "Tags": [],
            "InstanceId": "i-0414083bef5e91d94",
            "BlockDurationMinutes": 60,
            "SpotInstanceRequestId": "sir-r5bh7skq",
            "State": "closed",
            "ProductDescription": "Linux/UNIX",
            "LaunchedAvailabilityZone": "eu-north-1a",
            "LaunchSpecification": {
                "Placement": {
                    "Tenancy": "default",
                    "AvailabilityZone": "eu-north-1a"
                },
                "ImageId": "ami-6d27a913",
                "BlockDeviceMappings": [
                    {
                        "DeviceName": "/dev/sda1",
                        "VirtualName": "root",
                        "NoDevice": "",
                        "Ebs": {
                            "Encrypted": false,
                            "DeleteOnTermination": true,
                            "VolumeType": "gp2",
                            "VolumeSize": 8
                        }
                    }
                ],
                "EbsOptimized": false,
                "SecurityGroups": [
                    {
                        "GroupName": "default"
                    }
                ],
                "Monitoring": {
                    "Enabled": false
                },
                "InstanceType": "t3.nano",
                "AddressingType": "public",
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": 0,
                        "Description": "eth-zero",
                        "NetworkInterfaceId": "",
                        "DeleteOnTermination": true,
                        "SubnetId": "subnet-420ffc2b",
                        "AssociatePublicIpAddress": true
                    }
                ]
            },
            "Type": "one-time",
            "CreateTime": "2019-02-24T15:21:36.000Z",
            "SpotPrice": "0.008000"
        },
        {
            "Status": {
                "Message": "Your Spot request failed due to bad parameters.",
                "Code": "bad-parameters",
                "UpdateTime": "2019-02-24T14:51:48.000Z"
            },
            "ActualBlockHourlyPrice": "0.002000",
            "ValidUntil": "2019-03-03T14:51:47.000Z",
            "InstanceInterruptionBehavior": "terminate",
            "Tags": [],
            "Fault": {
                "Message": "Invalid device name /dev/sda",
                "Code": "InvalidBlockDeviceMapping"
            },
            "BlockDurationMinutes": 60,
            "SpotInstanceRequestId": "sir-1k9s5h3m",
            "State": "cancelled",
            "ProductDescription": "Linux/UNIX",
            "LaunchedAvailabilityZone": "eu-north-1a",
            "LaunchSpecification": {
                "Placement": {
                    "Tenancy": "default",
                    "AvailabilityZone": "eu-north-1a"
                },
                "ImageId": "ami-6d27a913",
                "BlockDeviceMappings": [
                    {
                        "DeviceName": "/dev/sda",
                        "VirtualName": "root",
                        "NoDevice": "",
                        "Ebs": {
                            "Encrypted": false,
                            "DeleteOnTermination": true,
                            "VolumeType": "gp2",
                            "VolumeSize": 8
                        }
                    }
                ],
                "EbsOptimized": false,
                "SecurityGroups": [
                    {
                        "GroupName": "default"
                    }
                ],
                "Monitoring": {
                    "Enabled": false
                },
                "InstanceType": "t3.nano",
                "AddressingType": "public",
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": 0,
                        "Description": "eth-zero",
                        "NetworkInterfaceId": "",
                        "DeleteOnTermination": true,
                        "SubnetId": "subnet-420ffc2b",
                        "AssociatePublicIpAddress": true
                    }
                ]
            },
            "Type": "one-time",
            "CreateTime": "2019-02-24T14:51:47.000Z",
            "SpotPrice": "0.011600"
        },
        {
            "Status": {
                "Message": "2019-02-24T15:02:17+0000 : 2019-02-24T15:02:17+0000 : Spot Instance terminated due to user-initiated termination.",
                "Code": "instance-terminated-by-user",
                "UpdateTime": "2019-02-24T15:03:34.000Z"
            },
            "ActualBlockHourlyPrice": "0.002000",
            "ValidUntil": "2019-03-03T14:55:26.000Z",
            "InstanceInterruptionBehavior": "terminate",
            "Tags": [],
            "InstanceId": "i-010442ac3cc85ec08",
            "BlockDurationMinutes": 60,
            "SpotInstanceRequestId": "sir-43x16b6n",
            "State": "closed",
            "ProductDescription": "Linux/UNIX",
            "LaunchedAvailabilityZone": "eu-north-1a",
            "LaunchSpecification": {
                "Placement": {
                    "Tenancy": "default",
                    "AvailabilityZone": "eu-north-1a"
                },
                "ImageId": "ami-6d27a913",
                "BlockDeviceMappings": [
                    {
                        "DeviceName": "/dev/sda1",
                        "VirtualName": "root",
                        "NoDevice": "",
                        "Ebs": {
                            "Encrypted": false,
                            "DeleteOnTermination": true,
                            "VolumeType": "gp2",
                            "VolumeSize": 8
                        }
                    }
                ],
                "EbsOptimized": false,
                "SecurityGroups": [
                    {
                        "GroupName": "default"
                    }
                ],
                "Monitoring": {
                    "Enabled": false
                },
                "InstanceType": "t3.nano",
                "AddressingType": "public",
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": 0,
                        "Description": "eth-zero",
                        "NetworkInterfaceId": "",
                        "DeleteOnTermination": true,
                        "SubnetId": "subnet-420ffc2b",
                        "AssociatePublicIpAddress": true
                    }
                ]
            },
            "Type": "one-time",
            "CreateTime": "2019-02-24T14:55:26.000Z",
            "SpotPrice": "0.011600"
        },
        {
            "Status": {
                "Message": "Your Spot request failed due to bad parameters.",
                "Code": "bad-parameters",
                "UpdateTime": "2019-02-24T14:29:24.000Z"
            },
            "ActualBlockHourlyPrice": "0.002000",
            "ValidUntil": "2019-03-03T14:29:23.000Z",
            "InstanceInterruptionBehavior": "terminate",
            "Tags": [],
            "Fault": {
                "Message": "Addressing type must be 'public'",
                "Code": "InvalidParameterCombination"
            },
            "BlockDurationMinutes": 60,
            "SpotInstanceRequestId": "sir-2jsh5brn",
            "State": "cancelled",
            "ProductDescription": "Linux/UNIX",
            "LaunchedAvailabilityZone": "eu-north-1a",
            "LaunchSpecification": {
                "Placement": {
                    "Tenancy": "default",
                    "AvailabilityZone": "eu-north-1a"
                },
                "ImageId": "ami-6d27a913",
                "BlockDeviceMappings": [
                    {
                        "DeviceName": "/dev/sda",
                        "VirtualName": "root",
                        "NoDevice": "",
                        "Ebs": {
                            "Encrypted": false,
                            "DeleteOnTermination": true,
                            "VolumeType": "gp2",
                            "VolumeSize": 8
                        }
                    }
                ],
                "EbsOptimized": false,
                "SecurityGroups": [
                    {
                        "GroupName": "default"
                    }
                ],
                "Monitoring": {
                    "Enabled": false
                },
                "InstanceType": "t3.nano",
                "AddressingType": "",
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": 0,
                        "Description": "eth-zero",
                        "NetworkInterfaceId": "",
                        "DeleteOnTermination": true,
                        "SubnetId": "subnet-420ffc2b",
                        "AssociatePublicIpAddress": true
                    }
                ]
            },
            "Type": "one-time",
            "CreateTime": "2019-02-24T14:29:23.000Z",
            "SpotPrice": "0.011600"
        },
        {
            "Status": {
                "Message": "Your spot request is fulfilled.",
                "Code": "fulfilled",
                "UpdateTime": "2019-02-24T15:37:28.000Z"
            },
            "ActualBlockHourlyPrice": "0.002000",
            "ValidUntil": "2019-03-03T15:37:26.000Z",
            "InstanceInterruptionBehavior": "terminate",
            "Tags": [],
            "InstanceId": "i-0a29e9de6d59d433f",
            "BlockDurationMinutes": 60,
            "SpotInstanceRequestId": "sir-z1e9591m",
            "State": "active",
            "ProductDescription": "Linux/UNIX",
            "LaunchedAvailabilityZone": "eu-north-1a",
            "LaunchSpecification": {
                "Placement": {
                    "Tenancy": "default",
                    "AvailabilityZone": "eu-north-1a"
                },
                "ImageId": "ami-6d27a913",
                "BlockDeviceMappings": [
                    {
                        "DeviceName": "/dev/sda1",
                        "VirtualName": "root",
                        "NoDevice": "",
                        "Ebs": {
                            "Encrypted": false,
                            "DeleteOnTermination": true,
                            "VolumeType": "gp2",
                            "VolumeSize": 8
                        }
                    }
                ],
                "EbsOptimized": false,
                "SecurityGroups": [
                    {
                        "GroupName": "default"
                    }
                ],
                "Monitoring": {
                    "Enabled": false
                },
                "InstanceType": "t3.nano",
                "AddressingType": "public",
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": 0,
                        "Description": "eth-zero",
                        "NetworkInterfaceId": "",
                        "DeleteOnTermination": true,
                        "SubnetId": "subnet-420ffc2b",
                        "AssociatePublicIpAddress": true
                    }
                ]
            },
            "Type": "one-time",
            "CreateTime": "2019-02-24T15:37:26.000Z",
            "SpotPrice": "0.008000"
        },
        {
            "Status": {
                "Message": "Your Spot request failed due to bad parameters.",
                "Code": "bad-parameters",
                "UpdateTime": "2019-02-24T14:33:43.000Z"
            },
            "ActualBlockHourlyPrice": "0.002000",
            "ValidUntil": "2019-03-03T14:33:42.000Z",
            "InstanceInterruptionBehavior": "terminate",
            "Tags": [],
            "Fault": {
                "Message": "Invalid device name /dev/sda",
                "Code": "InvalidBlockDeviceMapping"
            },
            "BlockDurationMinutes": 60,
            "SpotInstanceRequestId": "sir-n7c15y5p",
            "State": "cancelled",
            "ProductDescription": "Linux/UNIX",
            "LaunchedAvailabilityZone": "eu-north-1a",
            "LaunchSpecification": {
                "Placement": {
                    "Tenancy": "default",
                    "AvailabilityZone": "eu-north-1a"
                },
                "ImageId": "ami-6d27a913",
                "BlockDeviceMappings": [
                    {
                        "DeviceName": "/dev/sda",
                        "VirtualName": "root",
                        "NoDevice": "",
                        "Ebs": {
                            "Encrypted": false,
                            "DeleteOnTermination": true,
                            "VolumeType": "gp2",
                            "VolumeSize": 8
                        }
                    }
                ],
                "EbsOptimized": false,
                "SecurityGroups": [
                    {
                        "GroupName": "default"
                    }
                ],
                "Monitoring": {
                    "Enabled": false
                },
                "InstanceType": "t3.nano",
                "AddressingType": "public",
                "NetworkInterfaces": [
                    {
                        "DeviceIndex": 0,
                        "Description": "eth-zero",
                        "NetworkInterfaceId": "",
                        "DeleteOnTermination": true,
                        "SubnetId": "subnet-420ffc2b",
                        "AssociatePublicIpAddress": true
                    }
                ]
            },
            "Type": "one-time",
            "CreateTime": "2019-02-24T14:33:42.000Z",
            "SpotPrice": "0.011600"
        }
    ]
}
Stephen Ostermiller
  • 23,933
  • 14
  • 88
  • 109
Ярослав Рахматуллин
  • 7,599
  • 1
  • 41
  • 60
  • 4
    `column -t` made the trick to align the headers with the table itself. Thanks! – dimisjim Jan 14 '20 at 10:10
  • 4
    You can use `column -ts $'\t'` to split on tab characters but not spaces -- otherwise values with spaces will be split into multiple columns. From https://unix.stackexchange.com/a/57235/140650 – alexanderbird Dec 10 '20 at 21:15
  • @Stephen Ostermiller what's up with the mostly pointless edit? is it site-policy to "improve" other people's posts now days or are you just obsessing over some imaginary standard? what was the motivation for your edit? – Ярослав Рахматуллин Jul 06 '22 at 10:37
  • It is site policy to edit posts to improve them. I've been doing lots of edits on posts that don't use approved example domains. See this thread on meta about it: [What is the purpose of modifying the example domain in data example?](https://meta.stackoverflow.com/questions/418905/what-is-the-purpose-of-modifying-the-example-domain-in-data-example) – Stephen Ostermiller Jul 06 '22 at 12:45
20

The problem with the answers above is they only work if the fields are all about the same width.

To avoid this issue, the Linux column command could be used:

// input.json
[
  {
    "name": "George",
    "id": "a very very long field",
    "email": "george@domain.example"
  },
  {
    "name": "Jack",
    "id": 18,
    "email": "jack@domain.example"
  },
  {
    "name": "Joe",
    "id": 19,
    "email": "joe@domain.example"
  }
]

Then:

▶ jq -r '.[] | [.id, .name] | @tsv' input.json | column -ts $'\t'
a very very long field  George
18                      Jack
19                      Joe
Stephen Ostermiller
  • 23,933
  • 14
  • 88
  • 109
Alex Harvey
  • 14,494
  • 5
  • 61
  • 97
7

I made a mix with all responses to get all this behaviours

  • create header table
  • handle long fields
  • create a function to reuse

function bash

function jsonArrayToTable(){
     jq -r '(.[0] | ([keys[] | .] |(., map(length*"-")))), (.[] | ([keys[] as $k | .[$k]])) | @tsv' | column -t -s $'\t'   
}

Sample use

echo '[{"key1":"V1.1", "key2":"V2.1"}, {"keyA":"V1.2", "key2":"V2.2"}]' | jsonArrayToTable

output

key1  key2
----  ----
V1.1  V2.1
V2.2  V1.2
Bertrand Cedric
  • 653
  • 6
  • 11
1

If you want to generate an HTML table instead of a table for terminal output:

echo '[{
    "name": "George",
    "id": 12,
    "email": "george@domain.example"
}, {
    "name": "Jack",
    "id": 18,
    "email": "jack@domain.example"
}, {
    "name": "Joe",
    "id": 19,
    "email": "joe@domain.example"
}]' | jq -r 'map("<tr><td>" + .name + "</td><td>" + (.id | tostring) + "</td></tr>") | ["<table>"] + . + ["</table>"] | .[]'

Output:

<table>
<tr><td>George</td><td>12</td></tr>
<tr><td>Jack</td><td>18</td></tr>
<tr><td>Joe</td><td>19</td></tr>
</table>
sealocal
  • 10,897
  • 3
  • 37
  • 50
0

If the values don't contain spaces, this might be helpful:

read -r -a data <<<'name1 value1 name2 value2'

echo "name value"
echo "=========="

for ((i=0; i<${#data[@]}; i+=2)); do
  echo ${data[$i]} ${data[$((i+1))]}
done

Output

name value
==========
name1 value1
name2 value2
Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
  • I'm beginning to realise I can't get an array straight from jq, is that correct? So the way to go is to get a string from it (in a workable format) and go from there? – Rein Aug 25 '16 at 08:04
0

More simple implement:

jq -r '(.[0]|keys_unsorted|(.,map(length*"-"))),.[]|map(.)|@tsv'|column -ts $'\t'

you can add the following jq function into ~/.jq:

def pretty_table:
 (.[0]|keys_unsorted|(.,map(length*"-"))),.[]|map(.)|@tsv
 ;

and then run:

cat apps.json | jq -r pretty_table | column -ts $'\t'
Mr Z
  • 159
  • 2
  • 3