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] | @tsvAdding the headers can be done like so:
jq -r '["ID","NAME"], ["--","------"], (.[] | [.id, .name]) | @tsv'The result:
ID NAME-- ------12 George18 Jack19 Joelength*"-"
To automate the production of the line of dashes:
jq -r '(["ID","NAME"] | (., map(length*"-"))), (.[] | [.id, .name]) | @tsv'
Why not something like :
echo '[{ "name": "George", "id": 12, "email": "george@domain.com"}, { "name": "Jack", "id": 18, "email": "jack@domain.com"}, { "name": "Joe", "id": 19, "email": "joe@domain.com"}]' | jq -r '.[] | "\(.id)\t\(.name)"'Output
12 George18 Jack19 JoeEdit 1 : For fine grained formatting use tools like awk
echo '[{ "name": "George", "id": 12, "email": "george@domain.com"}, { "name": "Jack", "id": 18, "email": "jack@domain.com"}, { "name": "Joe", "id": 19, "email": "joe@domain.com"}]' | 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.com" , "george@domain2.com" ]}, { "name": "Jack", "id": 18, "email": [ "jack@domain3.com" , "jack@domain5.com" ]}, { "name": "Joe", "id": 19, "email": [ "joe@domain.com" ]}]' | jq -r '.[] | .email'Output
[ "george@domain1.com", "george@domain2.com"][ "jack@domain3.com", "jack@domain5.com"][ "joe@domain.com"]
Defining headers by hand is suboptimal! Omitting headers is also suboptimal.
TL;DR
data
[{ "name": "George", "id": 12, "email": "george@domain.com" },{ "name": "Jack", "id": 18, "email": "jack@domain.com" }, { "name": "Joe", "id": 19, "email": "joe@domain.com" }]script
[.[]| with_entries( .key |= ascii_downcase ) ] | (.[0] |keys_unsorted | @tsv) , (.[]|.|map(.) |@tsv)how to run
$ < data jq -rf script | column -tname id emailGeorge 12 george@domain.comJack 18 jack@domain.comJoe 19 joe@domain.comI 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 -tOutput:
state statuscode type blockprice created spotinstancerequestidclosed instance-terminated-by-user t3.nano 0.002000 2019-02-24T15:21:36.000Z sir-r5bh7skqcancelled bad-parameters t3.nano 0.002000 2019-02-24T14:51:47.000Z sir-1k9s5h3mclosed instance-terminated-by-user t3.nano 0.002000 2019-02-24T14:55:26.000Z sir-43x16b6ncancelled bad-parameters t3.nano 0.002000 2019-02-24T14:29:23.000Z sir-2jsh5brnactive fulfilled t3.nano 0.002000 2019-02-24T15:37:26.000Z sir-z1e9591mcancelled bad-parameters t3.nano 0.002000 2019-02-24T14:33:42.000Z sir-n7c15y5pInput:
$ 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" } ]}