Ruby - how to convert a large, multi-dimensional, array of hashes to CSV format

Question

I have a quite large array of hashes (stored in "@hash["response"]["results"])" returned by my program in JSON format.

I have seen several examples on Stack Overflow on how to convert a simple hash to CSV format, however I haven't been able to find any complex examples of doing it with a larger dataset.

I would like to use the hash keys ("pluginID", "ip", "pluginName", etc.) as the CSV headers and the hash values ("11112", "100.100.100.100", "Name for plugin here", etc.) for the CSV row content.

Note that the "repository" key is a hash itself and for that I'd like to just use the name, as opposed to the ID or description.

Any help is greatly appreciated. I have played with some code samples following the Ruby CSV standard library instructions but I am not even getting close.

@hash = '{
  "type": "regular",
  "response": {
    "Records": "137",
    "rRecords": 137,
    "startOffset": "0",
    "endOffset": "500",
    "matchingDataElementCount": "-1",
    "results": [
      { "pluginID": "11112",
        "ip": "100.100.100.100",
        "pluginName": "Name for plugin here",
        "firstSeen": "1444208776",
        "lastSeen": "1451974232",
        "synopsis": "synopsis contents",
        "description": "Full description would go here... Full description would go here... Full description would go here... Full description would go here... Full description would go here...",
        "solution": "",
        "version": "Revision: 1.51",
        "pluginText": "output text here",
        "dnsName": "name",
        "repository": {
          "id": "1",
          "name": "Name Here As Well",
          "description": "Description here also"
        },
        "pluginInfo": "11112 (0/6) Name for plugin here"
      },
      { "pluginID": "11113",
        "ip": "100.100.100.100",
        "pluginName": "Name for plugin here",
        "firstSeen": "1444455329",
        "lastSeen": "1451974232",
        "synopsis": "Tsynopsis contents",
        "description": "Full description would go here... Full description would go here... Full description would go here... Full description would go here... Full description would go here...",
        "solution": "",
        "version": "Revision: 1.51",
        "pluginText": "output text here",
        "dnsName": "name here",
        "repository": {
          "id": "1",
          "name": "Name Here As Well",
          "description": "Description here also"
        },
        "pluginInfo": "11112 (0/6) Name for plugin here"
      },
      { "pluginID": "11113",
        "ip": "100.100.100.100",
        "pluginName": "Name for plugin here : Passed",
        "firstSeen": "1444455329",
        "lastSeen": "1444455329",
        "synopsis": "nope, more synopsis data here",
        "description": "Uanother different description",
        "solution": "",
        "version": "Revision: 1.14",
        "pluginText": "",
        "dnsName": "name here",
        "repository": {
          "id": "1",
          "name": "Name Here As Well",
          "description": "Description here also"
        },
        "pluginInfo": "11114 (0/6) Name for plugin here : Passed"
      },
      { "pluginID": "11115",
        "ip": "100.100.100.100",
        "pluginName": "Name for plugin here",
        "firstSeen": "1444455329",
        "lastSeen": "1444455329",
        "synopsis": "Tsynopsis contents",
        "description": "Full description would go here... Full description would go here... Full description would go here... Full description would go here... Full description would go here...",
        "solution": "",
        "version": "Revision: 1.51",
        "pluginText": "output text here",
        "dnsName": "",
        "repository": {
          "id": "1",
          "name": "Name Here As Well",
          "description": "Description here also"
        },
        "pluginInfo": "11116 (0/6) Name for plugin here"
      }
    ]
  },
  "code": 0,
  "msg": "",
  "msg_det": [],
  "time": 1454733549
}'

Answer 1

This is pretty easy. There are essentially five steps:

1. Parse the JSON into a Ruby Hash.
2. Get the key names from the first hash in the "results" array^† and write them to the CSV file as headers.

3. Iterate over the "results" array and for each hash:

   1. Replace the "repository" hash with its "name" value.
   2. Extract the values in the same order as the headers and write them to the CSV file.

The code looks something like this:

require 'json'
require 'csv'

json = '{
  "type": "regular",
  "response": {
    ...
  },
  ...
}'

# Parse the JSON
hash = JSON.parse(json)

# Get the Hash we're interested in
results = hash['response']['results']

# Get the key names to use as headers
headers = results[0].keys

filename = "/path/to/output.csv"

CSV.open(filename, 'w', headers: :first_row) do |csv|
  # Write the headers to the CSV
  csv << headers

  # Iterate over the "results" hashes
  results.each do |result|
    # Replace the "repository" hash with its "name" value
    result['repository'] = result['repository']['name']

    # Get the values in the same order as the headers and write them to the CSV
    csv << result.values_at(*headers)
  end
end

^†This code (headers = results[0].keys) assumes that the first "results" hash will have all of the keys you want in the CSV. If that's not the case you need to either:

1. Specify the headers explicitly, e.g.:

   headers = %w[ pluginId ip pluginName ... ]
   

2. Loop over all of the hashes and build a list of all of their keys:

   headers = results.reduce([]) {|all_keys, result| all_keys | result.keys }
   

Answer 2

I used solution like it:

stats_rows = @hash["responce"]["results"].each_with_object([]) do |e, memo|
  memo << [e["pluginID"], e["ip"], e["pluginName"]]
end
CSV.generate do |csv|
  csv << ["pluginID", "ip", "pluginName"] #puts your hash keys into SCV
  stats_rows.each do |row| #values
     csv << row
  end
end