Question 270916 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Use jq to transform JSON output to CSV complex structure

I have an existing json file that I need to transform, but am having trouble with it.

Here’s an example of the structure:


jq -r '.document.results.summary.run.[] | [.run_number, .cinfo] ' input.json

[
  "42",
  [
    {
      "name": "Joe shmo",
      "state": "AZ",
      "city": "Phoenix"
    },
    {
      "name": "Jane shmo",
      "state": "CA",
      "city": "Blythe"
  ]
]
[
  "84",
  [
    {
      "name": "Jake shmo",
      "state": "NY",
      "city": "Albany"
    },
    {
      "name": "Jay shmo",
      "state": "TN",
      "city": "Franklin"
  ]
]

I need to convert this to a CSV to look like: 
"run_number","cinfo.name","cinfo.state","cinfo.city"

Example: 
"42","Joe shmo","AZ","Phoenix"
"42","Jane shmo","CA","Blythe"
"84","Jake shmo","NY","Albany"
"84","Jay shmo","TN","Franklin"

I tried to use | @csv I get:

jq: error (at input.json:1397): array ([{"@name":"...) is not valid in a csv row
I believe that this is because it is an array of multiple indexes. How can I transform the output using jq?

Tags:

2

Answers


  1. 0

    You tried applying @csv to an array containing non-strings. You have to decompose them to get to the values.

    jq -r '
  .document.results.summary.run.[] | . as {$run_number}
  | .cinfo[] | [$run_number, .name, .state, .city]
  | @csv
' input.json

    If all goes well (I could have tested it if you had provided input.json), this should produce

    "42","Joe shmo","AZ","Phoenix"
"42","Jane shmo","CA","Blythe"
"84","Jake shmo","NY","Albany"
"84","Jay shmo","TN","Franklin"
    Login or Signup to reply.
  2. 0

    You don’t have a valid JSON file in your code sample. Perhaps you meant to write this:

    [
  [
    "42",
    [
      {
        "name": "Joe shmo",
        "state": "AZ",
        "city": "Phoenix"
      },
      {
        "name": "Jane shmo",
        "state": "CA",
        "city": "Blythe"
      }
    ]
  ],
  [
    "84",
    [
      {
        "name": "Jake shmo",
        "state": "NY",
        "city": "Albany"
      },
      {
        "name": "Jay shmo",
        "state": "TN",
        "city": "Franklin"
      }
    ]
  ]
]

    In this case, you can use the command:

    jq -r '.[] | .[0] as $run_number | .[1][] | [$run_number, .name, .state, .city] | @csv' input.json

    and you will receive this result:

    "42","Joe shmo","AZ","Phoenix"
"42","Jane shmo","CA","Blythe"
"84","Jake shmo","NY","Albany"
"84","Jay shmo","TN","Franklin"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search