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

8

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"

answered Dec 9, 2017 at 20:46

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

2

I think the simplest ways to do it is

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

answered Dec 10, 2019 at 7:53

Phil

3,3721 gold badge23 silver badges17 bronze badges

Comments

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

0

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"

edited Dec 9, 2017 at 18:42

answered Dec 9, 2017 at 18:33

Rajiv Rai

2455 silver badges17 bronze badges

2 Comments

RomanPerekhrest Over a year ago

but the order of header columns is arbitrary in this case ... and columns don't correspond to subordinate values

Rajiv Rai Over a year ago

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