csvkit in2csv - how to convert a single json object to two-column csv

Question

Looking for a one liner with csvkit.

From a plain json object

{
  "whatever": 2342,
  "otherwise": 119,
  "and": 1,
  "so": 2,
  "on": 3
}

Want this csv

whatever,2342
otherwise,119
and,1
so,2
on,3

I basically want this command to work, but it doesn't.

echo $the_json | in2csv -f json
> When converting a JSON document with a top-level dictionary element, a key must be specified.

Seems like something csvkit can do, and I just haven't found the right options.

Answer 1

short answer

variant A: in2csv (csvkit) + csvtool

• wrap your json in brackets
• use in2csv's -I option to avoid unexpected behavior
• use a command to transpose the two-row CSV, e.g. csvtool

echo "[$the_json]" | in2csv -I -f json | csvtool transpose -

variant B: use jq instead

This is a solution using only jq: (https://stedolan.github.io/jq/)

echo "$the_json" | jq -r 'to_entries[] | [.key, .value] | @csv'

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

---

long answer (csvkit + csvtool)

the input

in2csv -f json expects a list of JSON objects, so you need to wrap the single object ({...}) into square brackets ([{...}]).

On POSIX compatible shells, write

echo "[$the_json]"

which will print

[{
  "whatever": 2342,
  "otherwise": 119,
  "and": 1,
  "so": 2,
  "on": 3
}]

the csvkit command

You may pipe the above data directly into in2csv. However, you might run into issues with the ”type inference“ (CSV data interpretation) feature of csvkit:

$ echo "[$the_json]" | in2csv -f json
whatever,otherwise,and,so,on
2342,119,True,2,3

1 has become True. For details, see the Tips and Troubleshooting part of the docs. It's suggested to turn off type inference using the -I option:

$ echo "[$the_json]" | in2csv -I -f json
whatever,otherwise,and,so,on
2342,119,1,2,3

Now the result is as expected

transpose the data

Still, you need to transpose the data. The csvkit docs say:

To transpose CSVs, consider csvtool.

(csvtool is available on github, opam, debian and probably other distribution channels.)

Using csvkit + csvtool, your final command looks like this:

echo "[$the_json]" | in2csv -I -f json | csvtool transpose -

with the hyphen (-) meaning to take the data from stdin. This is the result:

whatever,2342
otherwise,119
and,1
so,2
on,3

that's it.

I think there is no one-liner solution with csvtool only, you'll need in2csv. You may, however, use jq instead, see the short answer.

FTR, I'm using csvkit version 1.0.3.

Answer 2

Tested the first posted answer works! But it is a bit confusing because "[$the_json]" means the raw content of the json. So an example of command could be this:

echo '[{"a":"b","c":"d"}]' | in2csv -I -f json | csvtool transpose -

and if you want to do it with a file name instead, for instance myfile.json one can add the brackets with a sed command and pipe it to in2csv:

sed -e '1s/^/[/' -e 's/$/,/' -e '$s/,$/]/' myfile.json | in2csv -I -f json > myfile.csv

Example with the full transposition command:

sed -e '1s/^/[/' -e 's/$/,/' -e '$s/,$/]/' myfile.json | in2csv -I -f json | csvtool transpose - > myfile.csv

source: How to add bracket at beginning and ending in text on UNIX