如何在命令行中使用jq将JSON转换为CSV

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/