How to map an object to arrays so it can be converted to csv?

Question

I'm trying to convert an object that looks like this:

{
  "123" : "abc",
  "231" : "dbh",
  "452" : "xyz"
}

To csv that looks like this:

"123","abc"
"231","dbh"
"452","xyz"

I would prefer to use the command line tool jq but can't seem to figure out how to do the assignment. I managed to get the keys with jq '. | keys' test.json but couldn't figure out what to do next.

The problem is you can't convert a k:v object like this straight into csv with @csv. It needs to be an array so we need to convert to an array first. If the keys were named, it would be simple but they're dynamic so its not so easy.

Why not just gsub ':' for ',' ? – Mike Manfrin Nov 24 '13 at 21:52 — Mike Manfrin, Nov 24 '13 at 21:52
google `JSON to csv` ...lots of results – charlietfl Nov 24 '13 at 22:02 — charlietfl, Nov 24 '13 at 22:02

Jeff Mercado · Accepted Answer · 2016-11-23T18:26:31.223

79

Try this filter:

to_entries[] | [.key, .value]

to_entries converts an object to an array of key/value objects. [] breaks up the array to each of the items in the array
then for each of the items, covert to an array containing the key and value.

This produces the following output:

[
  "123",
  "abc"
],
[
  "231",
  "dbh"
],
[
  "452",
  "xyz"
]

Then you can use the @csv filter to convert the rows to CSV rows.

$ echo '{"123":"abc","231":"dbh","452":"xyz"}' | jq -r 'to_entries[] | [.key, .value] | @csv'
"123","abc"
"231","dbh"
"452","xyz"

edited Nov 23 '16 at 18:26

answered Jun 17 '14 at 01:11

Jeff Mercado

129,526
32
251
272

Hmm. For me this returns "`\"123\",\"abc\""` etc. I.e. each row is additionally quoted. – Konrad Rudolph Jan 28 '15 at 20:04
5

Make sure you add the `-r` flag to the call. You'll want to get the raw output. – Jeff Mercado Jan 29 '15 at 01:17
Thanks, I had overlooked that. – Konrad Rudolph Jan 29 '15 at 12:05
How do you use `to_entries` to a hash in the middle of JSON? All examples I've ever seen assume the target hash is at root. – Jari Turkia Apr 28 '21 at 08:16

score 5 · Answer 2 · answered Nov 06 '14 at 09:24

5

Jeff answer is a good starting point, something closer to what you expect:

cat input.json | jq 'to_entries | map([.key, .value]|join(","))'

[
 "123,abc",
 "231,dbh",
 "452,xyz"
]

But did not find a way to join using newline:

cat input.json | jq 'to_entries | map([.key, .value]|join(","))|join("\n")'

"123,abc\n231,dbh\n452,xyz"

answered Nov 06 '14 at 09:24

tomsoft

4,448
5
28
35

"\n" is the JSON representation of newline; to see it as a literal newline, use the -r command-lline option of jq. However, in general, using `@csv` to get CSV is the better way to go as @csv really does produce valid CSV. – peak Jun 30 '17 at 05:23

score 5 · Answer 3 · edited Dec 01 '16 at 05:07

Here's an example I ended up using this morning (processing PagerDuty alerts):

cat /tmp/summary.json | jq -r '
  .incidents
  | map({desc: .trigger_summary_data.description, id:.id})
  | group_by(.desc)
  | map(length as $len
  | {desc:.[0].desc, length: $len}) 
  | sort_by(.length) 
  | map([.desc, .length] | @csv)
  | join("\n") '

This dumps a CVS-separated document that looks something like: "[Triggered] Something annoyingly frequent",31 "[Triggered] Even more frequent alert!",35 "[No data] Stats Server is probably acting up",55

score 1 · Answer 4 · answered May 15 '18 at 11:15

1

Try This give same output you want

echo '{"123":"abc","231":"dbh","452":"xyz"}' | jq -r 'to_entries | .[] | "\"" + .key + "\",\"" + (.value | tostring)+ "\""'

answered May 15 '18 at 11:15

Sunil Shakya

8,097
2
17
20

score -3 · Answer 5 · answered Jun 19 '15 at 12:41

-3

onecol2txt () {
 awk 'BEGIN { RS="_end_"; FS="\n"}
   { for (i=2; i <= NF; i++){
       printf "%s ",$i 
       }
     printf "\n" 
   }'
}
cat jsonfile | jq -r -c '....,"_end_"' | onecol2txt

answered Jun 19 '15 at 12:41

kjetildm

274
3
11

How to map an object to arrays so it can be converted to csv?

5 Answers5

Linked