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

w8f9ii69 于 2022-11-19 发布在其他

关注(0)|答案(8)|浏览(181)

刚开始使用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命令应该包含一些过滤。

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

这样做的问题是，我不能像表格一样显示它。我知道jq有一些格式设置选项，但没有我使用printf时的选项好。我想我想在一个数组中得到这些值，然后我可以自己循环来进行格式设置...？我尝试的东西给予了我不同的结果，但从来没有我真正想要的。
谁能给我指个方向？

JSON

来源：https://stackoverflow.com/questions/39139107/how-to-format-a-json-string-as-a-table-using-jq

8条答案

按热度按时间

cngwdvgl1#

使用@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'

赞(0）回复(0）举报 2022-11-19

mitkmikd2#

为什么不这样做：

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得到一个包含数组的变量吗？
为什么不呢？
一个比较复杂的示例（实际上是根据您的示例修改的）演示了这一点，在该示例中，将email更改为数组

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

赞(0）回复(0）举报 2022-11-19

j0pj023g3#

手动定义标头不是最佳方法！省略标头也不是最佳方法。
TL;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"
        }
    ]
}

赞(0）回复(0）举报 2022-11-19

2ul0zpep4#

上面的答案的问题是，它们只有在字段宽度都差不多的情况下才起作用。
要避免此问题，可以使用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

赞(0）回复(0）举报 2022-11-19

wr98u20j5#

我把所有的React混合在一起，得到所有的行为

创建标题表
处理长字段
创建要重用的函数
函数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

赞(0）回复(0）举报 2022-11-19

1tuwyuhd6#

如果要生成 HTML 表格而不是终端输出表格：

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>

赞(0）回复(0）举报 2022-11-19

disbfnqx7#

如果值中不包含空格，这可能会有帮助：

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

赞(0）回复(0）举报 2022-11-19

vlju58qv8#

更简单的实现：

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'

赞(0）回复(0）举报 2022-11-19

我来回答

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

8条答案

`length*"-"`

数据

脚本

如何运行

相关问题

热门标签

最新问答