jq: Cannot index array with string "id"

Question

My backup.json looks like this:

{
  "andres": [
    [
      {
        "id": 1,
        "email": "password",
        "username": test1,
        "password": "[email protected]",
        "name": "Dummy Account",
        "address": "123 st road",,
        "ip_address": "0.0.0.0",
        "phone": "123-123-1234",
      },
      {
        "id": 2,
        "email": "[email protected]",
        "username": test2,
        "password": "password",
        "name": "Dummy Account",
        "address": "123 st road",,
        "ip_address": "0.0.0.0",
        "phone": "123-123-1234"
      }
    ],
  ]
}

I'm using the command:

jq -r '.andres[] | .id, .email, .username, .password, .name, .address, .ip_address, .phone' < backup.json > backup.csv

But it gives the error:

Cannot index array with string "id"

I want it to look like this:

1,[email protected],test1,password,Dummy Account,123 st road,0.0.0.0,123-123-1234
2,[email protected],test2,password,Dummy Account,123 st road,0.0.0.0,123-123-1234

I'm new to using JQ. Can someone please fix my command and tell me where i went wrong?

Thanks!

Solution 1

The value of andres in your json is an array of arrays, but you're accessing it as if it was an array of objects. You would have to flatten the arrays first (or index into) to access the objects. Then from those objects, you will want to map the values you want as csv as an array of values.

$ jq -r '
.andres[][] | [.id, .email, .username, .password, .name, .address, .ip_address, .phone] | @csv
' < backup.json > backup.csv

Note the second set of [] in .andres[][].

You may want to add some headers to your output as well.

$ jq -r '
["id", "email", "username", "password", "name", "address", "ip_address", "phone"] as $headers
    | $headers, (.andres[][] | [.[$headers[]]]) | @csv
' < backup.json > backup.csv

Solution 2

jq is a funny beast. I find it takes a lot of trial and error.

After fixing your JSON

$ jq -r '.andres[][] | map(values) | @csv' file.json
1,"password","test1","[email protected]","Dummy Account","123 st road","0.0.0.0","123-123-1234"
2,"[email protected]","test2","password","Dummy Account","123 st road","0.0.0.0","123-123-1234"

Note that for id=1, you have the password and email values switched.

See also "Format strings and escaping" in the jq manual

Comments

[ilovejq]

I updated the question a bit, can you re-read the edit as i explained a bit more into details; sorry for the confusion

[ilovejq]

To anyone wondering; future users who have a similar issue, i did not manually edit my .json; I just changed the way it was exported/created to not include the useless data.