Spartacus38 Spartacus38 - 13 days ago 7
JSON Question

Ruby JSON parse "sub-keys"?

I'm trying to parse an additional key to an existing JSON that I've been parsing for some time, but recently we add a new key "metric_details" that seperate individual areas, this contains 2 keys as well "performance_score", and "compliance_score". Now, I've been able to parse this without issue, but can't figure out for the life of me how to pull the metric_details out and also export the scores into my RDS.

Parse Code:

states = %w(CA TX)
results_warehouses = states
creds = User_creds

data_to_write = []
results_warehouses.each do |code|

dashboard = "https://sample.website.com/states/scorecard.json?state_id=#{code}"
puts "Pulling from #{dashboard}"

begin
data = HTTParty.get(dashboard, :basic_auth=> {:username => creds.public.text, :password => creds.private.text})
rescue
puts "Could not fetch data for #{code}. Skipping..."
next
end

types = %w(pack ship)
types.each do |type|

metric = data[type]
next if metric.nil?
metric.sort.each do |key|
state_id = key['state_id']
raw_date = key['date'].to_date
# Can't figure how to parse this metric_details = key['metric_details']
performance_score = key['performance_score']
audit_compliance_score = key['compliance_score']

data_to_write << "('#{state_id.to_s}',
'#{raw_date.to_s}',
'#{type.to_s}',
'#{metric_details.to_s}',
'#{performance_score.to_s}',
'#{compliance_score.to_s}')"
puts data_to_write
end
end

unless data_to_write.empty?
puts 'Inserting data'
db_conn = DatabaseConnect.new
db_client = db_conn.connect_mysql
ins = "INSERT IGNORE INTO `data`.`scoring` VALUES #{data_to_write.join(',')}"
db_client.query(ins)
end
db_conn.disconnect
puts 'Completed.'
end

puts 'All done!'


And, here is the JSON I'm pulling apart, which has no issues, until I try to grab the individual metric details

"pack":[
{
"state_id":"CA",
"total_score":90,
"compliance_score":100,
"performance_score":80,
"date":"2016-11-24T00:00:00.000-08:00",
"metric_details":{
"Damages":{
"performance_score":91,
"compliance_score":100
},
"Error Indicators":{
"performance_score":100,
"compliance_score":"N/A"
},
"Unverified":{
"performance_score":75,
"compliance_score":100
},
"Pack Miss":{
"performance_score":54,
"ompliance_score":100
}
}
}
],


And my table structure is fairly simple:

And essentially wanted to format as such:

CA, 2016-11-25, pack, Damages, 75, 100

state_id, raw_date, type, metric_details, performance_score, compliance_score

Thank you for the help!

Updated:

states = %w(CA TX)
results_warehouses = states
creds = User_creds

data_to_write = []
results_warehouses.each do |code|

dashboard = "https://sample.website.com/states/scorecard.json?state_id=#{code}"
puts "Pulling from #{dashboard}"

begin
data = HTTParty.get(dashboard, :basic_auth=> {:username => creds.public.text, :password => creds.private.text})
rescue
puts "Could not fetch data for #{code}. Skipping..."
next
end

types = %w(pack ship)
types.each do |type|

metric = data[type]
next if metric.nil?
metric.sort.each do |key|
state_id = key['state)ud']
raw_date = key['date'].to_date
hash = key['metric_details']
damages = hash['Damages']
performance_score = damages['performance_score']
audit_compliance_score = damages['compliance_score']

data_to_write << "('#{state_id.to_s}',
'#{raw_date.to_s}',
'#{type.to_s}',
'#{metric_details.to_s}',
'#{performance_score.to_s}',
'#{audit_compliance_score.to_s}')"
puts data_to_write
end
end

unless data_to_write.empty?
puts 'Inserting data'
db_conn = DatabaseConnect.new
db_client = db_conn.connect_mysql
ins = "INSERT IGNORE INTO `data`.`scoring` VALUES #{data_to_write.join(',')}"
db_client.query(ins)
end
db_conn.disconnect
puts 'Completed.'
end

puts 'All done!'

Answer

key['metric_details'] returns the hash:

{
  "Damages":{
    "performance_score":91,
    "compliance_score":100
  },
  "Error Indicators": {
    "performance_score":100,
    "compliance_score":"N/A"
  },
  ...
  ...
}

So, you need to look up the info in that hash:

hash = key['metric_details']
damages = hash['Damages']
performance_score = damages['performance_score']
compliance_score = damgages['compliance_score']

Here is a full example:

require 'json'

data = %Q{
{"pack":[
{
"state_id": "CA",
"total_score":90,
"compliance_score":100,
"performance_score":80,
"date":"2016-11-24T00:00:00.000-08:00",
"metric_details":{
"Damages":{
"performance_score":91,
"compliance_score":100
},
"Error Indicators":{
"performance_score":100,
"compliance_score":"N/A"
},
"Unverified":{
"performance_score":75,
"compliance_score":100
},
"Pack Miss":{
"performance_score":54,
"ompliance_score":100
}
}
}
]

}
}

data = JSON.parse data
data_to_write = ""

types = %w(pack ship)
types.each do |type|
  metric = data[type]
  next if metric.nil?

  metric.sort.each do |hash|
    state_id                = hash['state_id']
    raw_date                = hash['date']
    # Can't figure how to parse this metric_details          = key['metric_details']
    #performance_score       = key['performance_score']
    #audit_compliance_score  = key['compliance_score']
    metric_details = hash['metric_details']
    damages_hash = metric_details['Damages']
    performance_score = damages_hash['performance_score']
    compliance_score = damages_hash['compliance_score']

    data_to_write << "('#{state_id}',
                       '#{raw_date}',
                       '#{type}',
                       '#{performance_score}',
                       '#{compliance_score}')"
    puts data_to_write
  end
end

--output:--
('CA',
                       '2016-11-24T00:00:00.000-08:00',
                       'pack',
                       '91',
                       '100')