JQ how to filter an output

October 4, 2022

I am still early in my scripting and coding and new to linux so please bear with me. I have the following command that works but just not as well as I hope it can.

jq -r '.User[]|{Username: .username, Full_name: .full_name, Superuser: .is_superuser, Active: .is_active}' lbc.prod.xxx.yyyy.zzzz.json > lbc.prod.xxx.yyyy.zzzz_Users.csv

This produces a CSV with all the users on it. I would really like to narrow this down to just superusers. I have tried the following but it failed

jq -r '.User[]|{Username: .username, Full_name: .full_name, Superuser: .is_superuser, Active: .is_active} | select(.is_superuser = "true")' lbc.prod.xxx.yyyy.zzzz.json > lbc.prod.xxx.yyyy.zzzz_SuperUsers.csv

That didn’t work I also tried

jq -r '.User[]|{Username: .username, Full_name: .full_name, Superuser: .is_superuser{true}, Active: .is_active} ' lbc.prod.xxx.yyyy.zzzz.json > lbc.prod.xxx.yyyy.zzzz_SuperUsers.csv

but this produced errors and didn’t run.

>Solution :

If you want to filter on the property is_superuser, you need to apply the select filter before transforming the object to a new structure:

.User[]
| select(.is_superuser)
| {Username: .username, Full_name: .full_name, Superuser: .is_superuser, Active: .is_active}

or, if you want to filter the newly created object structure:

.User[]
| {Username: .username, Full_name: .full_name, Superuser: .is_superuser, Active: .is_active}
| select(.Superuser)

Note that this will generate one JSON object per line, not CSV. If you want CSV output, you have to transform to an array and filter through @csv:

.User[]
| select(.is_superuser)
| [ .username, .full_name, .is_superuser, .is_active ]
| @csv

and if you need headers for your CSV file:

["Username", "Full name", "Superuser", "Active"],
(.User[]
| select(.is_superuser)
| [ .username, .full_name, .is_superuser, .is_active ])
| @csv

To get raw CSV output, run the with --raw-output/-r flag of jq:

$ cat input.json
{
  "User": [
    {
      "username": "name1",
      "full_name": "full_name",
      "is_superuser": true,
      "is_active": false
    },
    {
      "username": "name1",
      "full_name": "full_name",
      "is_superuser": false,
      "is_active": false
    },
    {
      "username": "name2",
      "full_name": "full_name",
      "is_superuser": true,
      "is_active": false
    },
    {
      "username": "name2",
      "full_name": "full_name",
      "is_superuser": false,
      "is_active": false
    }
  ]
}

$ jq -r '["Username", "Full name", "Superuser", "Active"],
(.User[]
| select(.is_superuser)
| [ .username, .full_name, .is_superuser, .is_active ])
| @csv' input.json > output.csv

$ cat output.csv
"Username","Full name","Superuser","Active"
"name1","full_name",true,false
"name2","full_name",true,false