How to format a JSON string as a table using jq?

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

length*"-"

To automate the production of the line of dashes:

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

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

TL;DR

data

[{ "name": "George", "id": 12, "email": "[email protected]" },
{ "name": "Jack", "id": 18, "email": "[email protected]" },
{ "name": "Joe", "id": 19, "email": "[email protected]" }]

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  [email protected]
Jack    18  [email protected]
Joe     19  [email protected]

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"
        }
    ]
}

Why not something like:

echo '[{
    "name": "George",
    "id": 12,
    "email": "[email protected]"
}, {
    "name": "Jack",
    "id": 18,
    "email": "[email protected]"
}, {
    "name": "Joe",
    "id": 19,
    "email": "[email protected]"
}]' | 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": "[email protected]"
}, {
    "name": "Jack",
    "id": 18,
    "email": "[email protected]"
}, {
    "name": "Joe",
    "id": 19,
    "email": "[email protected]"
}]' | 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": [ "[email protected]" , "[email protected]" ]
}, {
    "name": "Jack",
    "id": 18,
    "email": [ "[email protected]" , "[email protected]" ]
}, {
    "name": "Joe",
    "id": 19,
    "email": [ "[email protected]" ]
}]' | jq -r '.[] | .email'

Output

[
  "[email protected]",
  "[email protected]"
]
[
  "[email protected]",
  "[email protected]"
]
[
  "[email protected]"
]

Tags:

Bash

Json

Jq