by Knut Melv?r

通過納特·梅爾瓦

如何在命令行中使用jq將JSON轉換為CSV (How to transform JSON to CSV using jq in the command line)

The shell tool jq is awesome for dealing with JSON-data. It can also transform that data into handy CSV-files, ready for all your spreadsheet wrangling needs.

外殼程序工具jq非常適合處理JSON數據。 它還可以將數據轉換為方便的CSV文件，從而可以滿足您所有電子表格的處理需求。

jq is an excellent little tool that lives in your terminal and does useful stuff with JSON-data. It’s a potent tool, but handy for the little things as well. For example, if you pipe JSON data to it, it prints it with syntax highlighting ? by default:

jq是一個出色的小工具，它駐留在您的終端中，并且可以處理JSON數據。 這是一個強大的工具，但對于一些小事情也很方便。 例如，如果您將JSON數據傳遞給它，它將以語法高亮顯示？ 默認：

$ cat some-data.json|jq

$ cat some-data.json|jq

You can install jq on most systems. (brew install jq on a Mac with homebrew / chocolatey install jq on windows with chocolatey). This post presents a more advanced jq technique. If you want to get the basics, you should check out the tutorial.

您可以在大多數系統上安裝jq 。 ( brew install jq在裝有自制軟件的Mac上chocolatey install jq / chocolatey install jq在裝有Chocolatey的 Windows上chocolatey install jq )。 這篇文章介紹了一種更高級的jq技術。 如果您想了解基礎知識，則應該閱讀本教程 。

jq works with any JSON source. Since I’m spending most of my days working with Sanity.io-based backends, I’ll use that as an example. Also because I think it’s immensely cool what we can do with this combination.

jq可與任何JSON源一起使用。 由于我大部分時間都在使用基于Sanity.io的后端，因此我將以此為例。 同樣是因為我認為使用此組合可以做的事非常酷。

Sanity is a backend for structured content and comes with a real-time API, and a query language called GROQ. You can interact with Sanity via HTTP and JS/PHP clients, but also with the CLI tool with $ sanity documents query 'GROQ-expression'.

Sanity是結構化內容的后端，并帶有實時API和稱為GROQ的查詢語言。 您可以通過HTTP和JS / PHP客戶端與Sanity進行交互，也可以通過帶有$ sanity documents query 'GROQ-expression'的CLI工具進行$ sanity documents query 'GROQ-expression' 。

So if you want your documents of the type post, you put $ sanity documents query '*[_type == "post"]'. Or if you just want those with a publish date in 2018, it’s$ sanity documents query '*[_type == "post" && publishedAt > "2018-01-01"]'. This query gives you whole documents. If you just wanted the titles, and publish dates, you’d write: *[_type == "post"]{title, publishedAt}.

因此，如果您希望使用post類型的文檔，則可以將$ sanity documents query '*[_type == "post"]'放在$ sanity documents query '*[_type == "post"]' 。 或者，如果您只希望發布日期在2018年的$ sanity documents query '*[_type == "post" && publishedAt > "2018-01-01 ，則使用$ sanity documents query '*[_type == "post" && publishedAt > "2018-01-01 ”]“。 該查詢為您提供整個文檔。 如果您只想要標題并發布日期，則可以寫出e: *[_type == "post"]{title, published于}。

You can pick out keys and values from JSON data in jq as well. Today we’re going to use it to transform structured content in a JSON array to a CSV file. Because your boss wants stuff in Excel sheets, right? Sit tight, and let’s dive in! ??

您也可以從jq JSON數據中選擇鍵和值。 今天，我們將使用它來將JSON數組中的結構化內容轉換為CSV文件。 因為您的老板想要Excel工作表中的內容，對嗎？ 坐好，讓我們開始吧！ ?

Let’s say you want a list of your blog entries’ titles, slugs and publish dates in a spreadsheet. The whole expression would look like this:

假設您要在電子表格中列出博客條目的標題，條目和發布日期。 整個表達式如下所示：

sanity documents query '*[_type == "post"]{title, "slug": slug.current, publishedAt}'|jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'

You can copy this and run with it or play with it on jqplay.com, but let’s see what’s going on in the jq-expression:

您可以復制并運行它，也可以在jqplay.com上使用它 ，但是讓我們看看jq -expression中發生了什么：

• -r is for --raw-ouput and makes sure that the output is plain old boring text without colors or special formatting.

  -r用于--raw-ouput ，并確保輸出為純舊的無聊文本，沒有顏色或特殊格式。

• (map(keys) | add | unique) as $cols iterates (map) through the keys in your object and adds unique ones to a variable called $cols. In other words, this is how your column headers are made.

  (map(keys) | add | unique) as $cols迭代( map )通過你的對象和按鍵add小號unique的人給一個變量叫$cols 。 換句話說，這就是列標題的制作方式。

• map(. as $row | $cols | map($row[.])) as $rows takes all objects in the outer array, and iterates through all the object keys (title, slug, publishedAt). It appends the values to an array, which gives you an array of arrays with the values, which is what you want when you're transforming JSON into CSV.

  map(. as $row | $cols | map($row[.])) as $rows獲取外部數組中的所有對象，并遍歷所有對象鍵(title，slug，publishedAt)。 它將值附加到數組，這將為您提供帶有值的數組數組，這是將JSON轉換為CSV時所需的值。

• $cols, $rows[] | @csv puts the column headers first in the array, and then each of the arrays that are transformed to lines by piping them to @csv , which formats the output as… csv.

  $cols, $rows[] | @csv $cols, $rows[] | @csv將列標題放在數組中，然后通過將它們通過管道傳遞到@csv將每個數組轉換為行，從而將輸出格式為…csv。

This command prints out the result in the shell. If you want to write it directly to a file, you can append > filename.csv to it, or, for example, to the clipboard (pipe it to | pbcopy if you’re on macOS). Or perhaps you'll do something exciting with the csv in pandas ?? in Python?

此命令將結果打印到外殼中。 如果要直接將其寫入文件，可以附加> filename. csv，或例如剪貼板(如果您使用的是macOS，請將其to | pbc管道to | pbc pbc opy)。 也許您會在pan das中使用csv做一些令人興奮的事情？ 在Python中？

If you found this useful, we'd love to hear all about it in the comment section!

如果您覺得此功能有用，我們很樂意在評論部分中聽到所有有關此信息的信息！

If you want to try out Sanity.io, you can go to sanity.io/freecodecamp and get an upped free developer plan. ?

如果您想試用Sanity.io，可以轉到sanity.io/freecodecamp并獲得升級的免費開發者計劃。 ?

Originally published at sanity.io.

最初在sanity.io上發布。

翻譯自: https://www.freecodecamp.org/news/how-to-transform-json-to-csv-using-jq-in-the-command-line-4fa7939558bf/