Convert json file to csv in linux jq command format

Question

I would like to convert json file in csv to a format we want. I would like to extract only specific variables from json and format it to csv.

Current json file

{
  "count": 86,
  "next": null,
  "previous": null,
  "results": [
    {
      "id": 7,
      "type": "organization",
      "url": "/api/v2/organizations/7/",
      "related": {
        "notification_templates": "/api/v2/organizations/7/notification_templates/",
        "notification_templates_started": "/api/v2/organizations/7/notification_templates_started/",
        "notification_templates_success": "/api/v2/organizations/7/notification_templates_success/",
        "notification_templates_error": "/api/v2/organizations/7/notification_templates_error/",
        "notification_templates_approvals": "/api/v2/organizations/7/notification_templates_approvals/",
        "object_roles": "/api/v2/organizations/7/object_roles/",
        "galaxy_credentials": "/api/v2/organizations/7/galaxy_credentials/"
      },
      "summary_fields": {
        },
        "modified_by": {
        },
          },
          "execute_role": {
            "description": "May run any executable resources in the organization",
            "name": "Execute",
            "id": 258
          },
          "project_admin_role": {
            "description": "Can manage all projects of the organization",
            "name": "Project Admin",
            "id": 263
          "credential_admin_role": {
            "description": "Can manage all credentials of the organization",
            "name": "Credential Admin",
            "id": 255
          },
          "workflow_admin_role": {
            "description": "Can manage all workflows of the organization",
            "name": "Workflow Admin",
            "id": 261
          "job_template_admin_role": {
            "description": "Can manage all job templates of the organization",
            "name": "Job Template Admin",
            "id": 262
          },
          "read_role": {
            "description": "May view settings for the organization",
            "name": "Read",
            "id": 256
          },
          "approval_role": {
            "description": "Can approve or deny a workflow approval node",
            "name": "Approve",
            "id": null
          }
        },
        "user_capabilities": {
          "edit": true,
          "delete": true
        },
        "related_field_counts": {
          "inventories": 0,
          "teams": 0,
          "users": 1,
          "job_templates": 0,
          "admins": 2,
          "projects": 0
        }
      },
      "custom_virtualenv": null
    },
    {
      "id": 36,
      "type": "organization",
      "url": "/api/v2/organizations/36/",
      "related": {
        "modified_by": "/api/v2/users/444/",
        "users": "/api/v2/organizations/36/users/",
        "admins": "/api/v2/organizations/36/admins/",
        "teams": "/api/v2/organizations/36/teams/",
        "credentials": "/api/v2/organizations/36/credentials/",
        "applications": "/api/v2/organizations/36/applications/",
        "activity_stream": "/api/v2/organizations/36/activity_stream/",
        "notification_templates": "/api/v2/organizations/36/notification_templates/",
        "notification_templates_started": "/api/v2/organizations
        "notification_templates_approvals": "/api/v2/organizations/36/notification_templates_approvals/",
        "object_roles": "/api/v2/organizations/36/object_roles/",
        "access_list": "/api/v2/organizations/36/access_list/",
        },
        "object_roles": {
          "execute_role": {
            "description": "May run any executable resources in the organization",
            "name": "Execute",
            "id": 26573
          },
          "project_admin_role": {
            "description": "Can manage all projects of the organization",
            "name": "Project Admin",
            "id": 26574
          },
          "inventory_admin_role": {
            "description": "Can manage all inventories of the organization",
            "name": "Inventory Admin",
            "id": 26575
          },
          "credential_admin_role": {
            "description": "Can manage all credentials of the organization",
            "name": "Credential Admin",
            "id": 26576
          "notification_admin_role": {
            "description": "Can manage all notifications of the organization",
            "name": "Notification Admin",
            "id": 26578
          },
          "job_template_admin_role": {
            "description": "Can manage all job templates of the 
          "auditor_role": {
            "description": "Can view all aspects of the organization",
            "name": "Auditor",
            "id": 26580
          "read_role": {
            "description": "May view settings for the organization",
            "name": "Read",
            "id": 26582
          },
          "approval_role": {
            "description": "Can approve or deny a workflow approval node",
            "name": "Approve",
            "id": 26583
    {
      "id": 21,
      "type": "organization",
      "url": "/api/v2/organizations/21/",
      "related": {
        "created_by": "/api/v2/users/1/",
        "modified_by": "/api/v2/users/1/",
        "projects": "/api/v2/organizations/21/projects/",
        "inventories": "/api/v2/organizations/21/inventories/",
        "job_templates": "/api/v2/organizations/21/job_templates/",
        "workflow_job_templates": "/api/v2/organizations/21/workflow_job_templates/",
        "notification_templates_error": "/api/v2/organizations/21/notification_templates_error/",
        "notification_templates_approvals": "/api/v2/organizations/21/notification_templates_approvals/",
        "object_roles": "/api/v2/organizations/21/object_roles/",
        "access_list": "/api/v2/organizations/21/access_list/",
        "instance_groups": "/api/v2/organizations/21/instance_groups/",
        "galaxy_credentials": "/api/v2/organizations/21/galaxy_credentials/"
      },
      "summary_fields": {
        "created_by": {
          "id": 1,
          "username": "admin",
          "first_name": "",
          "last_name": ""
        },
        "object_roles": {
          "admin_role": {
            "description": "Can manage all aspects of the organization",
            "name": "Admin",
            "id": 4736,
            "user_only": true
          },
          "execute_role": {
            "description": "May run any executable resources in the organization",
            "name": "Execute",
            "id": 4737
          },
          "project_admin_role": {
            "description": "Can manage all projects of the organization",
            "name": "Project Admin",
            "id": 4738
          "credential_admin_role": {
            "description": "Can manage all credentials of the organization",
            "name": "Credential Admin",
            "id": 4740
          },
          "workflow_admin_role": {
            "description": "Can manage all workflows of the organization",
            "name": "Workflow Admin",
            "id": 4741
          },
          "notification_admin_role": {
dmin_role": {
            "description": "Can manage all job templates of the organization",
            "name": "Job Template Admin",
            "id": 4743
          },
          "auditor_role": {
            "description": "Can view all aspects of the organization",
            "name": "Auditor",
            "id": 4744
          "read_role": {
            "description": "May view settings for the organization",
            "name": "Read",
            "id": 4746
          },
          "approval_role": {
            "description": "Can approve or deny a workflow approval node",
            "name": "Approve",
            "id": null
          }
        },
        "user_capabilities": {
          "edit": true,
          "delete": true
        },
        "related_field_counts": {
          "inventories": 6,
    },

The field that I want to extract and format into csv is "id" "type"

How do I use linux jq commands to extract those? Extract specific variables in the json to csv

Does this answer your question? How to transform nested JSON to csv using jq — pmf
– pmf, Commented Mar 6, 2023 at 10:45

Marcin Kłopotek · Accepted Answer · 2023-03-07 07:08:36Z

Assuming the JSON provided in the example is valid (actually it's not, it seems to be truncated). You can get the expected CSV output using the jq query:

["id", "type", "inventories", "teams", "users", "job_templates", "admins", "projects", "name", "description"] as $headers |
($headers,
  (.results[] | [ 
    .id,
    .type,
    .summary_fields.related_field_counts.inventories,
    .summary_fields.related_field_counts.teams,
    .summary_fields.related_field_counts.users,
    .summary_fields.related_field_counts.job_templates,
    .summary_fields.related_field_counts.admins,
    .summary_fields.related_field_counts.projects,
    .name,
    .description
  ])
) | @csv

You can find the working output in this jqplay snippet.

Putting the query and the jq command together (assuming the input.json is your JSON contents used in the example):

jq --raw-output '["id","type","inventories","teams","users","job_templates","admins","projects","name","description"] as $headers | ($headers, (.results[] | [ .id, .type, .summary_fields.related_field_counts.inventories, .summary_fields.related_field_counts.teams, .summary_fields.related_field_counts.users, .summary_fields.related_field_counts.job_templates, .summary_fields.related_field_counts.admins, .summary_fields.related_field_counts.projects, .name, .description ])) | @csv' input.json

protob · Accepted Answer · 2023-03-06 13:56:22Z

2

To extract these fields to csv, you can use jq with @csv filter in a shell script like this:

#!/bin/bash

json=$(cat input.json)

fields=$(echo "$json" | jq -r '.results[] | [
    .id,
    .type,
    .summary_fields.related_field_counts.inventories,
    .summary_fields.related_field_counts.teams,
    .summary_fields.related_field_counts.users,
    .summary_fields.related_field_counts.job_templates,
    .summary_fields.related_field_counts.admins,
    .summary_fields.related_field_counts.projects,
    .name,
    .description
] | @csv')

echo "id,type,inventories,teams,users,job_templates,admins,projects,name,description" > output.csv
echo "$fields" >> output.csv

edited Mar 6, 2023 at 13:56

answered Mar 6, 2023 at 11:23

protob

3,6211 gold badge11 silver badges24 bronze badges

8 Comments

I__sh Over a year ago

This is not working. Only I can see id,type,inventories,teams,users,job_templates,admins,projects,name,description ,,,,,,,,, as a csv result

protob Over a year ago

You had a comma "," at the end in the example which i assumed was a typo. Do you have array of objects in your json? The script should work assuming the json has the structure you provided in the example, that is a single object.

I__sh Over a year ago

Oh this same format is repeating several times. Which means this whole format is divided in , comma.

protob Over a year ago

Ok for array try with .[] operator in jq, i updated my answer. It should work now

protob Over a year ago

Thx, you must select .results[] in jq then. I edited my answer

|

Collectives™ on Stack Overflow

Convert json file to csv in linux jq command format

2 Answers 2

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related