1

The below is my json code

[
{
    "kind": "user",
    "version": "v2",
    "metadata": {
      "name": "person1"
    },
    "spec": {
      "roles": [
        "access",
        "auditor",
        "editor",
        "abc-access"
      ],
      "status": {
        "is_locked": false,
        "locked_time": "0001-01-01T00:00:00Z",
        "lock_expires": "0001-01-01T00:00:00Z",
        "recovery_attempt_lock_expires": "0001-01-01T00:00:00Z"
      },
      "expires": "0001-01-01T00:00:00Z",
      "created_by": {
        "time": "2023-06-07T08:50:58.559675944Z",
        "user": {
          "name": "person2"
        }
      }
    }
  },
  {
    "kind": "user",
    "version": "v2",
    "metadata": {
      "name": "person2"
    },
    "spec": {
      "roles": [
        "access",
        "auditor",
        "editor",
        "abc-access"
      ],
      "status": {
        "is_locked": false,
        "locked_time": "0001-01-01T00:00:00Z",
        "lock_expires": "0001-01-01T00:00:00Z",
        "recovery_attempt_lock_expires": "0001-01-01T00:00:00Z"
      },
      "expires": "0001-01-01T00:00:00Z",
      "created_by": {
        "time": "2023-06-06T14:01:15.256905162Z",
        "user": {
          "name": "person5"
        }
      }
    }
  },
  {
    "kind": "user",
    "version": "v2",
    "metadata": {
      "name": "person3"
    },
    "spec": {
      "roles": [
        "access",
        "auditor",
        "editor",
        "abc-access"
      ],
      "status": {
        "is_locked": false,
        "locked_time": "0001-01-01T00:00:00Z",
        "lock_expires": "0001-01-01T00:00:00Z",
        "recovery_attempt_lock_expires": "0001-01-01T00:00:00Z"
      },
      "expires": "0001-01-01T00:00:00Z",
      "created_by": {
        "time": "2023-06-15T12:16:09.35013254Z",
        "user": {
          "name": "person2"
        }
      }
    }
  },
  {
    "kind": "user",
    "version": "v2",
    "metadata": {
      "name": "person3"
    },
    "spec": {
      "roles": [
        "access",
        "auditor",
        "editor",
        "abc-access"
      ],
      "status": {
        "is_locked": false,
        "locked_time": "0001-01-01T00:00:00Z",
        "lock_expires": "0001-01-01T00:00:00Z",
        "recovery_attempt_lock_expires": "0001-01-01T00:00:00Z"
      },
      "expires": "0001-01-01T00:00:00Z",
      "created_by": {
        "time": "2023-06-06T13:45:30.543520308Z",
        "user": {
          "name": "person6"
        }
      }
    }
  },
  {
    "kind": "user",
    "version": "v2",
    "metadata": {
      "name": "person4"
    },
    "spec": {
      "roles": [
        "access",
        "auditor",
        "editor",
        "abc-access"
      ],
      "status": {
        "is_locked": false,
        "locked_time": "0001-01-01T00:00:00Z",
        "lock_expires": "0001-01-01T00:00:00Z",
        "recovery_attempt_lock_expires": "0001-01-01T00:00:00Z"
      },
      "expires": "0001-01-01T00:00:00Z",
      "created_by": {
        "time": "2023-06-07T04:49:41.39245724Z",
        "user": {
          "name": "person5"
        }
      }
    }
  },
  {
    "kind": "user",
    "version": "v2",
    "metadata": {
      "name": "person5"
    },
    "spec": {
      "roles": [
        "access",
        "auditor",
        "editor",
        "abc-access"
      ],
      "traits": {
        "aws_role_arns": null,
        "db_names": null,
        "db_users": null,
        "kubernetes_groups": null,
        "kubernetes_users": null,
        "logins": [
          "ec2-user",
          "root"
        ],
        "windows_logins": null
      },
      "status": {
        "is_locked": false,
        "locked_time": "0001-01-01T00:00:00Z",
        "lock_expires": "0001-01-01T00:00:00Z",
        "recovery_attempt_lock_expires": "0001-01-01T00:00:00Z"
      },
      "expires": "0001-01-01T00:00:00Z",
      "created_by": {
        "time": "2023-06-06T13:51:39.173360152Z",
        "user": {
          "name": "person6"
        }
      }
    }
  },
  {
    "kind": "user",
    "version": "v2",
    "metadata": {
      "name": "person6"
    },
    "spec": {
      "roles": [
        "access",
        "editor",
        "auditor"
      ],
      "traits": {
        "aws_role_arns": null,
        "azure_identities": null,
        "db_names": null,
        "db_roles": null,
        "db_users": null,
        "gcp_service_accounts": null,
        "kubernetes_groups": null,
        "kubernetes_users": null,
        "logins": [
          "root",
          "ubuntu",
          "ec2-user"
        ],
        "windows_logins": null
      },
      "status": {
        "is_locked": false,
        "locked_time": "0001-01-01T00:00:00Z",
        "lock_expires": "0001-01-01T00:00:00Z",
        "recovery_attempt_lock_expires": "0001-01-01T00:00:00Z"
      },
      "expires": "0001-01-01T00:00:00Z",
      "created_by": {
        "time": "2023-06-06T13:41:41.579010534Z",
        "user": {
          "name": "xyz"
        }
      }
    }
  }
]

I'm converting it in to csv with

jq -r '.[] | [ .metadata.name ] ,  [ .spec.roles[] ] | @csv' ind.json

The output of above command in as below

"person1"
"alpha","gamma","beta","abc-access"
"person2"
"alpha","gamma","beta","abc-access"
"person3"
"alpha","gamma","beta","abc-access"
"person3"
"alpha","gamma","beta","abc-access"
"person4"
"alpha","gamma","beta","abc-access"
"person5"
"alpha","gamma","beta","abc-access"
"person6"
"alpha","gamma","beta","auditor"

I want the name and roles in different coloumn. I want the name and roles in the same line with sufficient space so that if we copy paster in excel, it will be two coloumn, one with "name" and other with "role". Also looking for options if we get output without double quotes

Below is the expected output

person1    alpha,gamma,beta,abc-access
person2    alpha,gamma,beta,abc-access
person3    alpha,gamma,beta,abc-access
person3    alpha,gamma,beta,abc-access
person4    alpha,gamma,beta,abc-access
person5    alpha,gamma,beta,abc-access
person6    alpha,gamma,beta,auditor
Improve this question
6
  • Please edit your question and add the exact output you are expecting from this example so we can know if we are giving you what you need.
    – terdon ♦
    Commented Sep 29, 2023 at 15:28
  • The number of roles varies, how do you want to handle that? Do you want to repeat the name for each role?
    – Kusalananda ♦
    Commented Sep 29, 2023 at 15:58
  • I have updated the expected output. Could you please check
    – code userit
    Commented Sep 29, 2023 at 16:14
  • What is the delimiter between the first and the second column?
    – Kusalananda ♦
    Commented Sep 29, 2023 at 16:15
  • The delimiter is just space between first and second column. @Kusalananda
    – code userit
    Commented Sep 29, 2023 at 16:19

1 Answer 1

Reset to default
1

I'm assuming you want two tab-delimited columns where the second column is a simple comma-delimited list of strings.

$ jq -r '["name","roles"], map([.metadata.name, (.spec.roles|join(","))])[] | @tsv' file
name    roles
person1 access,auditor,editor,abc-access
person2 access,auditor,editor,abc-access
person3 access,auditor,editor,abc-access
person3 access,auditor,editor,abc-access
person4 access,auditor,editor,abc-access
person5 access,auditor,editor,abc-access
person6 access,editor,auditor

The jq expression passes a set of 2-element arrays through @tvs to format them for tab-delimited output. The first such array is the static header, while the rest comes from a map() call. This map() call pulls out the name from the metadata structure and then creates a simple comma-delimited string from of the roles strings from the spec structure.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .