如何使用 jq 将 JSON 字符串格式化为表?

刚刚开始使用 Bash 脚本,偶然发现 jq 可以使用 JSON。

我需要将下面这样的 JSON 字符串转换为用于在终端中输出的表。

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

我想在终端机上显示的内容:

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

注意,我不想显示每一行的 email 属性,所以 jq 命令应该包含一些过滤。下面给出了一个简单的姓名和 ID 列表:

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

问题是,我不能像显示表一样显示它。我知道 jq 有一些格式化选项,但是没有我使用 printf时的选项好。我想把这些值放在一个数组里,然后我可以自己循环进行格式化... ?我尝试的事情给了我不同的结果,但从来没有什么我真正想要的。

有人能告诉我正确的方向吗?

118061 次浏览

为什么不像这样:

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)"'

输出

12  George
18  Jack
19  Joe

编辑1: 对于细粒度格式,请使用像 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"

编辑2: 回复

我不可能直接得到一个包含数组的变量 JQ 的?

为什么不呢?

一个有点复杂的例子(实际上是从你的修改过来的) ,其中电子邮件被更改为一个数组说明了这一点

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'

输出

[
"george@domain1.example",
"george@domain2.example"
]
[
"jack@domain3.example",
"jack@domain5.example"
]
[
"joe@domain.example"
]

如果值不包含空格,这可能会有所帮助:

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

输出

name value
==========
name1 value1
name2 value2

使用 @tsv过滤器有很多值得推荐的地方,主要是因为它以一种标准的方式处理大量的“边缘情况”:

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

添加标题可以这样做:

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

结果是:

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

正如@Tobia 所指出的,您可能希望通过使用 column对 jq 生成的结果进行后处理来设置表的格式以供查看。如果您使用类似 bash 的 shell,那么 column -ts $'\t'应该是非常可移植的。

length*"-"

使破折号线的生成自动化:

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

手工定义头是次优的! 省略头也是次优的。

DR

资料

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

剧本

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

怎么跑

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

产出:

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

输入:

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

上述答案的问题在于,它们只有在字段宽度相同的情况下才有效。

为了避免这个问题,可以使用 Linux column命令:

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

然后:

▶ jq -r '.[] | [.id, .name] | @tsv' input.json | column -ts $'\t'
a very very long field  George
18                      Jack
19                      Joe

我把所有的反应混合在一起,得到了所有这些行为

  • 创建头表
  • 处理长长的田地
  • 创建一个可重用的函数

函数 bash

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

样本使用情况

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

输出

key1  key2
----  ----
V1.1  V2.1
V2.2  V1.2

如果希望为终端输出生成 超文本标示语言表而不是表:

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>"] | .[]'

产出:

<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>

更简单的方法:

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

可以在 ~/.jq中添加以下 jq 函数:

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

然后跑:

cat apps.json | jq -r pretty_table | column -ts $'\t'