Convert JSON array into CSV using jq

Question

I have JSON file called temp.json.

{ "users": [ { "username": "jack", "email": "[email protected]", "total running apps": "1", "api-mock-app": "0", "flogo": "1", "ipaas": "0", "nodejs-app": "0" }, { "username": "jill", "email": "[email protected]", "total running apps": "1", "api-mock-app": "0", "flogo": "1", "ipaas": "0", "nodejs-app": "0" } ] }

i want to convert this JSON into CSV lilke this,

username email total running apps api-mock-app flogo ipaas nodejs-app jack [email protected] 1 0 1 0 0 jill [email protected] 1 0 1 0 0

I tried this

jq -r '.users[] | keys[0] [.username, .email, ."total running apps", ."api-mock-app", .flogo, .ipaas, ."nodejs-app"] | join(", ") | @csv' temp.json`

But i am getting error

q: error (at temp.json:22): Cannot index string with string "jack"`

Can anyone explain where am i making mistake and please let me know the correct answer.

Possible duplicate of Convert a JSON into CSV using jq

knb
– knb

2017-12-10 08:59:28 +00:00
Commented Dec 10, 2017 at 8:59 — knb
– knb, Commented Dec 10, 2017 at 8:59

RomanPerekhrest · Accepted Answer · 2017-12-09 20:46:21Z

jq solution:

jq -r '(.users[0] | keys_unsorted), (.users[] | to_entries | map(.value))|@csv' temp.json

The output:

"username","email","total running apps","api-mock-app","flogo","ipaas","nodejs-app" "jack","[email protected]","1","0","1","0","0" "jill","[email protected]","1","0","1","0","0"

Phil · Accepted Answer · 2019-12-10 07:53:27Z

I think the simplest ways to do it is

jq -r "(.users[0] | keys_unsorted), (.users[] | map(.) | @csv)"

Rajiv Rai · Accepted Answer · 2017-12-09 18:42:09Z

I tried this, and it worked perfectly,

jq -r '(.users[0] | keys), (.users[] | [.username, .email, ."total running apps", ."api-mock-app", .flogo, .ipaas, ."nodejs-app"]) | @csv' temp.json

output format is.

"api-mock-app","email","flogo","ipaas","nodejs-app","total running apps","username" "jack","[email protected]","1","0","1","0","0" "jill","[email protected]","1","0","1","0","0"

but the order of header columns is arbitrary in this case ... and columns don't correspond to subordinate values
Yeah, I realised that, keys_unsorted would solve this problem as you have rightly answered, Thank You.

Collectives™ on Stack Overflow

Convert JSON array into CSV using jq

3 Answers 3

Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Linked

Related