如何使用jq將嵌套的JSON轉換為csv

我嘗試在 Linux cmd 行上使用 jq 將以下格式的 json 轉換為 csv，但沒有成功。任何指導幫助將不勝感激。

{
    "dir/file1.txt": [
      {
        "Setting": {
          "SettingA": "",
          "SettingB": null
        },
        "Rule": "Rulechecker.Rule15",
        "Description": "",
        "Line": 11,
        "Link": "www.sample.com",
        "Message": "Some message",
        "Severity": "error",
        "Span": [
          1,
          3
        ],
        "Match": "[id"
      },
      {
        "Setting": {
          "SettingA": "",
          "SettingB": null
        },
        "Check": "Rulechecker.Rule16",
        "Description": "",
        "Line": 27,
        "Link": "www.sample.com",
        "Message": "Fix the rule",
        "Severity": "error",
        "Span": [
          1,
          3
        ],
        "Match": "[id"
      }
    ],
    "dir/file2.txt": [
      {
        "Setting": {
          "SettingA": "",
          "SettingB": null
        },
        "Rule": "Rulechecker.Rule17",
        "Description": "",
        "Line": 51,
        "Link": "www.example.com",
        "Message": "Fix anoher 'rule'?",
        "Severity": "error",
        "Span": [
          1,
          18
        ],
        "Match": "[source,terminal]\n----\n"
      }
    ]
}

最終，我想呈現一個矩陣，其中dir/file1.txt、dir/file2.txt作為矩陣左側的行，所有鍵都作為列標題呈現，并帶有相應的值。

| Filename  | SettingA | SettingB | Rule | More columns... |
| -------- | -------------- | -------------- | -------------- | -------------- |
| dir/file1.txt    |             | null | Rulechecker.Rule15 | |
| dir/file1.txt   |             | null | Rulechecker.Rule16 | |
| dir/file2.txt   |             | null | Rulechecker.Rule17  | |                       

uj5u.com熱心網友回復：

遍歷獲得的頂級鍵值對to_entries以訪問鍵名，然后再次遍歷其內容陣列.value以獲取陣列項。另請注意，樣本最后一個.Match值中存在的換行符不能按面向行的格式（如 CSV）使用。在這里，我選擇使用文字字串替換\n它們gsub。

jq -r '
  to_entries[] | . as {$key} | .value[] | [$key,
    (.Setting | .SettingA, .SettingB),
    .Rule // .Check, .Description, .Line, .Link,
    .Message, .Severity, .Span[], .Match
    | strings |= gsub("\n"; "\\n")
  ] | @csv
'

"dir/file1.txt","",,"Rulechecker.Rule15","",11,"www.sample.com","Some message","error",1,3,"[id"
"dir/file1.txt","",,"Rulechecker.Rule16","",27,"www.sample.com","Fix the rule","error",1,3,"[id"
"dir/file2.txt","",,"Rulechecker.Rule17","",51,"www.example.com","Fix anoher 'rule'?","error",1,18,"[source,terminal]\n----\n"

演示

如果您只想按照它們出現的順序轉儲所有值，您可以通過使用.. | scalars遍歷檔案的級別來簡化它：

jq -r '
  to_entries[] | . as {$key} | .value[] | [$key,
    (.. | scalars) | strings |= gsub("\n"; "\\n")
  ] | @csv
'

"dir/file1.txt","",,"Rulechecker.Rule15","",11,"www.sample.com","Some message","error",1,3,"[id"
"dir/file1.txt","",,"Rulechecker.Rule16","",27,"www.sample.com","Fix the rule","error",1,3,"[id"
"dir/file2.txt","",,"Rulechecker.Rule17","",51,"www.example.com","Fix anoher 'rule'?","error",1,18,"[source,terminal]\n----\n"

演示

至于列標題，對于第一種情況，我會手動添加它們，因為無論如何你都會拼出每個值路徑。對于后一種情況，它會有點復雜，因為并非所有列都有直接名稱（陣列的專案應該Span被稱為什么？），并且有些似乎發生了變化（在第二條記錄中，列Rule被稱為Check）。但是，您可以堅持使用第一條記錄的名稱，并按原樣采用最深的欄位名稱或添加陣列索引。這些方面的事情會做：

jq -r '
  to_entries[0].value[0] | ["Filename", (
    path(..|scalars) | .[.[[map(strings)|last]]|last:] | join(".")
  )] | @csv
'

"Filename","SettingA","SettingB","Rule","Description","Line","Link","Message","Severity","Span.0","Span.1","Match"

演示

標籤：jsonlinuxCSVjq

上一篇：通過真正絕對路徑的后綴子字串查找檔案

下一篇：如何重寫除一個特定URI之外的所有請求