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
2
Answers
To extract these fields to csv, you can use
jqwith@csvfilter in a shell script like this: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
jqquery:You can find the working output in this jqplay snippet.
Putting the query and the
jqcommand together (assuming theinput.jsonis your JSON contents used in the example):