How to convert json into csv file using jq?

Question

This is my json file:

{
  "ClientCountry": "ca",
  "ClientASN": 812,
  "CacheResponseStatus": 404,
  "CacheResponseBytes": 130756,
  "CacheCacheStatus": "hit"
}
{
  "ClientCountry": "ua",
  "ClientASN": 206996,
  "CacheResponseStatus": 301,
  "CacheResponseBytes": 142,
  "CacheCacheStatus": "unknown"
}
{
  "ClientCountry": "ua",
  "ClientASN": 206996,
  "CacheResponseStatus": 0,
  "CacheResponseBytes": 0,
  "CacheCacheStatus": "unknown"
}

I want to convert these json into csv like below.

"ClientCountry", "ClientASN","CacheResponseStatus", "CacheResponseBytes", "CacheCacheStatus"
"ca", 812, 404, 130756, "hit";
"ua", 206996, 301, 142,"unknown";
"ua", 206996, 0,0,"unknown";

Please let me know how to achieve this using jq?

I just tried below. But its not working.

jq 'to_entries[] | [.key, .value] | @csv'

Regards Palani

Answer 1

Since you want all the key-values, then assuming that the keys are presented in a consistent order in the input file, you can simply write:

jq -r '[.[]] | @csv' palanikumar.json

With the given input, this produces the following CSV:

"ca",812,404,130756,"hit"
"ua",206996,301,142,"unknown"
"ua",206996,0,0,"unknown"

Adding the headers and the trailing semicolons (if you really want them) is left as a (very easy) exercise.

Inconsistent ordering

If the ordering of the keys varies or might vary, then the following could be used to produce suitable CSV, assuming that the ordering of the keys in the first object in the input stream should be used:

input
| . as $first
| keys_unsorted as $keys
| $keys, [$first[]], (inputs | [.[$keys[]]]) | @csv

The appropriate invocation of jq would include both the -n and -r command-line options.

Answer 2

Look at these links

How to convert arbirtrary simple JSON to CSV using jq?

http://bigdatums.net/2017/09/30/convert-json-to-csv-with-jq/

( jq -r '.myarray | @csv' )