How convert JSON to TSV in bash

Question

I am trying to convert JSON file from the command below to CSV format:

curl -X GET 'https://api.coinex.com/v1/market/kline?market=BCHBTC&type=1min' -H "Accept: application/json"

I tried jq command as following, but it was unsuccessful :

jq '.data |to_entries[] | @csv'

OR

jq '.data |to_entries[] | [.key,  (.0|tonumber),(.1|tonumber),(.2|tonumber),(.3|tonumber),(.4|tonumber),(.5|tonumber),(.6|tonumber),(.7|tonumber)   )] | @csv'

desired output is like:

            1619094720  0.01738857  0.01742868  0.01742868  0.01737360  1.24151689  0.0215974650849251  BCHBTC
            1619094780  0.01742823  0.01742913  0.01742913  0.01742807  0.89060000  0.0155215977170000  BCHBTC
            1619094840  0.01744941  0.01745423  0.01745423  0.01744941  0.42820000  0.0074724290140000  BCHBTC
            1619094900  0.01745458  0.01740857  0.01745458  0.01740857  0.73530000  0.0128245470890000  BCHBTC

That is not CSV, are you looking for tab-separated output?. Is `jq -r '.data[] | @tsv'` what you are looking for? — Inian, Apr 22 '21 at 14:30
Does this answer your question? [How to format a JSON string as a table using jq?](https://stackoverflow.com/questions/39139107/how-to-format-a-json-string-as-a-table-using-jq) — Inian, Apr 22 '21 at 14:55

score 1 · Answer 1 · edited Dec 04 '21 at 17:38

1

There is no need for to_entries, just apply the filter directly to the arrays, e.g.:

jq -r '.data[] | .[0:-1] | map(tonumber) | @tsv'

Note that the above removes the "BCHBTC" text field, as noted by peak in the comments this can be included with the special or operator (//):

jq -r '.data[] | tonumber? // . | @tsv'

edited Dec 04 '21 at 17:38

cigien

57,834
11
73
112

answered Apr 22 '21 at 15:14

Thor

45,082
11
119
130

why take the slice? – peak Apr 23 '21 at 11:57
@peak: otherwise `tonumber` complains, anyway the removed text-field can easily be made redundant – Thor Apr 29 '21 at 14:27
Given OP has confirmed wanting to keep the text field, you could use `tonumber? // .` – peak Apr 29 '21 at 15:34
@peak: Nice I didn't know you could use the `//` operator like this. I have included your suggestion – Thor Apr 29 '21 at 15:53
You would still need `map` though if using @tsv, it makes little practical difference. – peak Apr 29 '21 at 16:28

score 1 · Accepted Answer · answered Dec 04 '21 at 16:34

1

jq -r '.data[] | @tsv ' solved the problem, thanks @ Inian

answered Dec 04 '21 at 16:34

mehdi shokati

29
7

Please don't add "thanks" as answers. They don't actually provide an answer to the question, and can be perceived as noise by its future visitors. Once you [earn](https://meta.stackexchange.com/questions/146472/what-is-the-best-way-to-increase-my-reputation-and-privileges) enough [reputation](https://stackoverflow.com/help/whats-reputation), you will gain privileges to [upvote answers](https://stackoverflow.com/help/privileges/vote-up) you like. This way future visitors of the question will see a higher vote count on that answer, and the answerer will also be rewarded with reputation points. – Tyler2P Dec 04 '21 at 22:37

How convert JSON to TSV in bash

2 Answers2