如何使用jq将JSON数组生成包含house字段的表格格式输出

我有如下JSON数据：

[
   {
      "users": [
         {
            "name": "alfred",
            "points": 10
         },
         {
            "name": "alice",
            "points": 11
         },
         {
            "name": "emma",
            "points": 33
         }
      ],
      "house": "red"
   },
   {
      "users": [
         {
            "name": "Logan",
            "points": 20
         },
         {
            "name": "Keith",
            "points": 19
         },
         {
            "name": "Lisa",
            "points": 18
         },
         {
            "name": "Julia",
            "points": 45
         }
      ],
      "house": "blue"
   }
]

请问如何生成如下表格格式的输出？需要将house字段的值显示在对应行中。

期望输出表格 #

|Name  |  Points|House|
|alfred|    10  |red  |
|alice |    11  |red  |
|emma  |    33  |red  |
|Logan |    20  |blue |
|Keith |    19  |blue |
|Lisa  |    18  |blue |
|Julia |    45  |blue |

我尝试过的命令 #

% jq '.[]|.users[]| .name' test.json

"alfred"
"alice"
"emma"
"Logan"
"Keith"
"Lisa"
"Julia"

% jq '.[]|.users[]| (.name, .points)' test.json

"alfred"
10
"alice"
11
"emma"
33
"Logan"
20
"Keith"
19
"Lisa"
18
"Julia"
45

可以使用以下jq命令生成目标表格：

jq -r '
"|Name  |  Points|House|",
.[] | .house as $house |
.users[] |
"|" + .name + (if (.name | length) < 6 then " " * (6 - (.name | length)) else "" end) + "|    " + (.points | tostring) + "  |" + $house + "  |"
' test.json

命令说明： #

• -r：输出原始字符串，避免JSON格式的引号包裹
• .house as $house：将当前分组的house值存入变量，供该组所有用户行复用
• 字符串拼接逻辑：通过计算用户名长度补充空格，保证和表头对齐；将数字类型的points转为字符串，避免格式冲突

执行该命令后即可得到符合要求的表格输出。

内容的提问来源于stack exchange，提问作者Logan Palanisamy