CSV格式的grep JSON数据值

Question

我正在尝试通过curl获取JSON数据，并使用jq命令将其转换为CSV。

代码：

#!/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 

以下是JSON数据的示例结构：

{"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"}}}]}

我想要grep数据项(id、name、symbol、slug、num_market_pairs等)的值。忽略：“tags”。

所需的输出格式：

    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

尝试grep .price的值，例如：

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

错误：

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

已尝试：

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

错误：

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

我也尝试过：Stackoverflow Related Topic

Answer 1

这个问题涉及到选择，而不是"grep"，下面是相应的解决方案：

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

如果将倒数第二行展开为：

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

.price

如果你只想要“价格”，你可以考虑这样的查询：

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

或

.. | select(has("quote")?) 
| .. | select(has("price")?).price