如何使用JQ 1.4将复杂的JSON转换为CSV

问题描述

我正在Windows 64位计算机上使用JQ 1.4.

I am using JQ 1.4 on Windows 64 bit machine.

下面是输入文件IP.txt

{
  "results": [
    {
      "name": "Google",
      "employees": [
        {
          "name": "Michael",
          "division": "Engineering"
        },
        {
          "name": "Laura",
          "division": "HR"
        },
        {
          "name": "Elise",
          "division": "Marketing"
        }
      ]
    },
    {
      "name": "Microsoft",
      "employees": [
        {
          "name": "Brett",
          "division": "Engineering"
        },
        {
          "name": "David",
          "division": "HR"
        }
      ]
    }
  ]
}

{
  "results": [
    {
      "name": "Amazon",
      "employees": [
        {
          "name": "Watson",
          "division": "Marketing"
        }
      ]
    }
  ]
}

文件包含两个"results".第一个结果包含2个公司的信息:Google和Microsoft.第二个结果包含有关Amazon的信息.我想将此JSON转换为带有公司名称和员工名称的csv文件.

File contains two "results". 1st result containts information for 2 companies: Google and Microsoft. 2nd result contains information for Amazon.I want to convert this JSON into csv file with company name and employee name.

"Google","Michael"
"Google","Laura"
"Google","Elise"
"Microsoft","Brett"
"Microsoft","David"
"Amazon","Watson"

我能够编写以下脚本:

jq -r "[.results[0].name,.results[0].employees[0].name]|@csv" IP.txt

"Google","Michael"

"Amazon","Watson"

有人可以指导我编写脚本而无需对索引值进行硬编码吗?

Can someone guide me to write the script without hardcoding the index values?

脚本应该能够为任意数量的results以及任意数量的公司的每个包含信息生成输出.

Script should be able generate output for any number results and each cotaining information of any number of companies.

我尝试使用下面的脚本，但未生成预期的输出:

I tried using below script which didn't generate expected output:

jq -r "[.results[].name,.results[].employees[].name]|@csv" IP.txt
"Google","Microsoft","Michael","Laura","Elise","Brett","David"
"Amazon","Watson"

推荐答案

您需要先将结果简化为公司名称和员工名称.然后，您可以将其转换为csv行.

You need to flatten down the results first to rows of company and employee names. Then with that, you can convert to csv rows.

map(.results | map({ cn: .name, en: .employees[].name } | [ .cn, .en ])) | add[] | @csv

由于有输入流，因此必须将其插入(-s).由于要输出csv，因此将要使用原始输出(-r).

Since you have a stream of inputs, you'll have to slurp (-s) it in. Since you want to output csv, you'll want to use raw output (-r).

这篇关于如何使用JQ 1.4将复杂的JSON转换为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！