12

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

Improve this question

2 Answers 2

Reset to default
14

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.

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

1 Comment

Bro I tried a lot to get headers. But couldnt get command to extract headers in csv format. Tried this jq -r 'keys[]' sample.json. Please help
0

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' )

Improve this answer

3 Comments

@Palanikumar You tried adding -r ? ( jq -r )
Well, check the answer of @peak, and retry
From what I see, this is the problem [.key, .value]

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.