1

I am trying to get JSON data by curl and convert it to CSV with the jq command.

Code:

#!/bin/bash
 
    #Remove previously generated output files 
    rm datacsv.csv  output.json 
   #Get crypto pairs data from the server (curl) in JSON from the COINAMRKET.COM server, save output.json 
              curl -H "X-CMC_PRO_API_KEY: 45f65521-ad1b-4dbb-9c33-743ba7a63e68" -H "Accept: application/json" -d "start=1&limit=2&convert=USD" -G https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest>output.json 
                             
     #Convert JSON to CSV, Outputfile: datacsv.csv                               
      jq -r '.data[0]  | @csv'  output.json  >datacsv.csv

The following is the sample structure of the JSON data:

{"status":{"timestamp":"2021-04-24T15:51:59.165Z","error_code":0,"error_message":null,"elapsed":12,"credit_count":1,"notice":null,"total_count":4824},"data":[{"id":1,"name":"Bitcoin","symbol":"BTC","slug":"bitcoin","num_market_pairs":9553,"date_added":"2013-04-28T00:00:00.000Z","tags":["mineable","pow","sha-256","store-of-value","state-channels","coinbase-ventures-portfolio","three-arrows-capital-portfolio","polychain-capital-portfolio","binance-labs-portfolio","arrington-xrp-capital","blockchain-capital-portfolio","boostvc-portfolio","cms-holdings-portfolio","dcg-portfolio","dragonfly-capital-portfolio","electric-capital-portfolio","fabric-ventures-portfolio","framework-ventures","galaxy-digital-portfolio","huobi-capital","alameda-research-portfolio","a16z-portfolio","1confirmation-portfolio","winklevoss-capital","usv-portfolio","placeholder-ventures-portfolio","pantera-capital-portfolio","multicoin-capital-portfolio","paradigm-xzy-screener"],"max_supply":21000000,"circulating_supply":18690125,"total_supply":18690125,"platform":null,"cmc_rank":1,"last_updated":"2021-04-24T15:51:02.000Z","quote":{"USD":{"price":50199.80286231888,"volume_24h":54839569429.87117,"percent_change_1h":1.35143147,"percent_change_24h":1.22964148,"percent_change_7d":-17.56716272,"percent_change_30d":-2.51591082,"percent_change_60d":7.55313111,"percent_change_90d":56.29697475,"market_cap":938240590472.0977,"last_updated":"2021-04-24T15:51:02.000Z"}}}]}

I want to grep the value of the items in data (id,name,symbol,slug, num_market_pairs, etc.) ignoring: “tags”.

Desired output format:

    data__id    data__name  data__symbol    data__slug  data__num_market_pairs  data__date_added    data__max_supply    data__circulating_supply    data__total_supply  data__platform  data__cmc_rank  data__last_updated  data__quote__USD__price data__quote__USD__volume_24h    data__quote__USD__percent_change_1h data__quote__USD__percent_change_24h    data__quote__USD__percent_change_7d data__quote__USD__percent_change_30d    data__quote__USD__percent_change_60d    data__quote__USD__percent_change_90d    data__quote__USD__market_cap    data__quote__USD__last_updated
1   Bitcoin BTC bitcoin 9553    2013-04-28T00:00:00.000Z    21000000    18690125    18690125    null    1   2021-04-24T15:51:02.000Z    50199.8028623189    54839569429.8712    1.35143147  1.22964148  -17.56716272    -2.51591082 7.55313111  56.29697475 938240590472.098    2021-04-24T15:51:02.000Z

Tried to grep the values of .price for example:

jq '.data|to_entries[] |[.key, (.price| (.price|tonumber))] | @csv' output.json >csv1.csv

Error:

jq: error (at output.json:0): null (null) cannot be parsed as a number

Tried:

jq -r '.[]|@csv '  output.json >csv1.csv

Error:

jq: error (at output.json:0): object ({"timestamp...) cannot be csv-formatted, only array

Also tried: Stackoverflow Related Topic

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
mehdi shokati
  • 29
  • 7

1 Answers1

0

The problem involves selection rather than "grep", and here is a solution accordingly:

.data[0]
| [paths(scalars) | select( all(.[]; type=="string")) ] as $paths
| ($paths |  map(["data"] + . | join("__"))),
  [ getpath($paths[]) ]
| @tsv

The last-but-one line can perhaps be more easily understood if unwrapped as:

[ $paths[] as $p | getpath($p) ]

.price

If you just want the "price", you could consider a query along the lines of:

.. | objects | select(has("price")).price

or

.. | select(has("quote")?) 
| .. | select(has("price")?).price
peak
  • 105,803
  • 17
  • 152
  • 177
  • Thanks a lot for your answer, it solved 'CSV' to 'JSON' conversion, also I like to grep specific fields and ignore the rest(id,name,symbol,slug, num_market_pairs, etc.). I tried guideline like (https://stackoverflow.com/questions/33895076/exclude-column-from-jq-json-output) but got the following error: 'jq: error (at output.json:0): Cannot index string with string "id" ' – mehdi shokati Apr 26 '21 at 15:09
  • Thanks a lot and I will add another question to filter the output – mehdi shokati Apr 26 '21 at 16:02
  • Tx. Did you notice the new section on ".price"? – peak Apr 26 '21 at 16:13
  • Because, I am new user of jq, I dont know how excatly to place '.. | objects | select(has("price")).price' in the command. I tried some, but didnt get the filtered output and trying to answer a new question about filtering the output 'csv'. – mehdi shokati Apr 26 '21 at 16:25