Use jq to Convert json File to csv

Question

I am using curl to pull Alien Vault OTX pulses from their API, the initial output I receive is in json format and I need to convert this json into csv as so it can be read by some other software. I aim to use jq as many others have recommended it.

{   "count": 1210,   "next": "https://otx.alienvault.com/api/v1/pulses/subscribed?page=2",   "results": [
    {
      "industries": [],
      "tlp": "white",
      "description": "Tropic Trooper (also known as KeyBoy) levels its campaigns against Taiwanese, Philippine, and Hong Kong targets, focusing on their government, healthcare, transportation, and high-tech industries. Its operators are believed to be very organized and develop their own cyberespionage tools that they fine-tuned in their recent campaigns. Many of the tools they use now feature new behaviors, including a change in the way they maintain a foothold in the targeted network.",
      "created": "2018-03-14T17:24:48.014000",
      "tags": [
        "china",
        "keyboy",
        "tropic trooper"
      ],
      "modified": "2018-03-14T17:24:48.014000",
      "author_name": "AlienVault",
      "public": 1,
      "extract_source": [],
      "references": [
        "https://blog.trendmicro.com/trendlabs-security-intelligence/tropic-trooper-new-strategy/"
      ],
      "targeted_countries": [],
      "indicators": [
        {
          "indicator": "CVE-2018-0802",
          "description": "",
          "created": "2018-03-14T17:25:03",
          "title": "",
          "content": "",
          "type": "CVE",
          "id": 406248965
        },
        {
          "indicator": "fb9c9cbf6925de8c7b6ce8e7a8d5290e628be0b82a58f3e968426c0f734f38f6",
          "description": "",
          "created": "2018-03-14T17:25:03",
          "title": "",
          "content": "",
          "type": "FileHash-SHA256",
          "id": 438581959
        }
      ],
      "more_indicators": false,
      "revision": 1,
      "adversary": "Tropic Trooper",
      "id": "5aa95ae02781860367e354e4",
      "name": "Tropic Troopers New Strategy"
    }

I am looking to use jq to extract certain fields and convert to csv. My expected output would look something like:

"CVE-2018-0802","CVE"
"tibetnews.today","domain"
"02281e26e89b61d84e2df66a0eeb729c5babd94607b1422505cd388843dd5456","FileHash-SHA256"

So far I have tried:

<AV.json jq -r '.results.indicators[] | [.indicator, .type] | @csv' AV.csv

Any help is greatly appreciated.

Cheers,

George

Answer 1

.results is an array so you'll have to expand it too. This can be done either by:

 .results[] | .indicators[] | [.indicator, .type] | @csv

or more compactly:

 .results[].indicators[] | [.indicator, .type] | @csv

You'll also have to direct the output to the designated file, e.g.:

  jq -r -f program.jq < AV.json > AV.csv

Output

"CVE-2018-0802","CVE"
"fb9c9cbf6925de8c7b6ce8e7a8d5290e628be0b82a58f3e968426c0f734f38f6","FileHash-SHA256"