Question 109308 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

Convert json file to csv in linux jq command format

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search