CODEWITHSUNDEEP
jsoncsvjq
user3594235
user3594235

Reputation: 35

Converting json into csv with jq while only capturing specific keys

This is my first post so apologies if I make mistakes. Consider I have the following json output

{
  "records": [
    {
      "title": "root logon",
      "login": "61819009",
      "uid": "ajsd879asdjksasda123asd1asd1",
      "password": "OizfD19jC$ySaV$MKpSF",
      "login_url": "http://192.168.0.1/",
      "notes": ""
    },
    {
      "title": "important admin account",
      "login": "admin",
      "uid": "asdjhkasdh89eoajdiuas98ue9aoi",
      "password": "0z5gDUC#Rb354TlLq$KJ",
      "login_url": "",
      "notes": "",
      "folders": [
        {
          "shared_folder": "Department",
          "folder": "Important",
          "option1": false,
          "option2": false
        }
      ]
    }
    {
      "title": "another important admin",
      "login": "[email protected]",
      "uid": "asjhe98asiajsijeouiaueiaiu",
      "password": "3pUs@uXEqsxCv7PRkDlJ",
      "login_url": "http://192.168.0.2/",
      "notes": ""
    },
    {
      "title": "switch admin",
      "login": "admin",
      "uid": "asjhe89ausiodjakljskea90ik",
      "password": "hMB!eMsAE8q4aDQuM4LY",
      "login_url": "",
      "notes": "",
      "folders": [
        {
          "shared_folder": "Department2",
          "folder": "network\\switches",
          "option1": false,
          "option2": false
        }
      ]
    }
  ]
}

from the list above, I would like to only export title, login, uid, shared folder, and folder fields to csv file. When I run the following command

jq --raw-output '.records[] | [.title, .login, .uid, .shared_folder, .folder] | @ tsv > file

what I am expecting as a CSV output is

title login uid shared_folder folder
root logon 61819009 ajsd879asdjksasda123asd1asd1
important admin account admin asdjhkasdh89eoajdiuas98ue9aoi Department Important
another important admin [email protected] asjhe98asiajsijeouiaueiaiu
switch admin admin asjhe89ausiodjakljskea90ik Department2 network\switches

instead I'm getting the following CSV output:

title login uid
root logon 61819009 ajsd879asdjksasda123asd1asd1
important admin account admin asdjhkasdh89eoajdiuas98ue9aoi
another important admin [email protected] asjhe98asiajsijeouiaueiaiu
switch admin admin asjhe89ausiodjakljskea90ik

my goal is to eliminate capturing following fields

Upvotes: 0

Views: 142

Answers (2)

aborruso
aborruso

Reputation: 5688

You an error in your JSON, you must add a ,.

Once done, the command is this

jq --raw-output '.records[] | [.title, .login, .uid, .folders[0].shared_folder,.folders[0].folder] |@tsv'

Upvotes: 1

peak
peak

Reputation: 116780

Here's one way:

.records[]
| [.title, .login, .uid] + ((.folders[]? // null) | [.shared_folder, .folder])
| @tsv

Upvotes: 0

Related Questions