Convert Json to CSV by jq filter-CodePudding

I have a json file with this content and want to convert it to CSV like below:

{
  "fields": [
    {
      "id": 17,
      "name": "Business Division",
      "values": [
        {
          "id": 131,
          "name": "Accounting",
          "industry": [
            "Accounting"
          ]
        }
      ]
    },
    {
      "id": 16,
      "name": "Cancellation Reason",
      "values": [
        {
          "id": 114,
          "name": "Forgot"
        }
      ]
    }
  ]
}

CSV File format:

17,Business Division,131,Accounting,Accounting
16,Cancellation Reason,114,Forgot

I ran this command on the terminal:

jq -M -r -f industry.jq source.json |tr -d '"' >source.csv

this is the content of industry.jq file that is used as the filter:

.fields[] 
| .values[] as $e
| $e.industry[]? as $s
| [.id, .name, $e.id, $e.name, $s? ]
| @csv

As result, the second line of the CSV file did not print I think it's because of the .industry[] object that did not available in the second object in my Json

How can I print the above json in the needed format?

CodePudding user response：

.fields[] | [ .id, .name, .values[].id, .values[].name, .values[].industry[]? ] | @csv

Will produce

17,"Business Division",131,"Accounting","Accounting"
16,"Cancellation Reason",114,"Forgot"

When invoked like:

jq --raw-output '.fields[] | [ .id, .name, .values[].id, .values[].name, .values[].industry[]? ] | @csv'

Multiple industries will be added behind

Try it online

CodePudding user response：

I'd use try ... catch ... to make the value of the default explicit, e.g.

(try $e.industry[] catch null) as $s