I am trying to seed data to my database from a JSON array of objects. I have two separate database tables - Property and Unit (a Property has many Units). I have been able to do this for the property information (property model) successfully with the API requesting the JSON data and then seeding this to the database however the ValuationReport attribute is showing on my front end as a JSON object in string form.
Here is a sample of the JSON data I am working with (I've limited this sample to an array of 3 objects for demonstration purposes):
[
{
"PublicationDate": "17/09/2019",
"PropertyNumber": 2198231,
"County": "CAVAN",
"LocalAuthority": "CAVAN COUNTY COUNCIL",
"Valuation": 15090,
"Category": "OFFICE",
"Uses": "OFFICE (OVER THE SHOP), -",
"Address1": "77(1ST & 2ND FL) MAIN STREET",
"Address2": "CAVAN",
"Address3": "CO. CAVAN",
"Address4": "",
"Address5": "",
"CarPark": 0,
"Xitm": 641927.73,
"Yitm": 804638.3,
"ValuationReport": [
{
"Level": "1",
"FloorUse": "RESTAURANT",
"Area": 112.25,
"NavPerM2": 80,
"Nav": 8980
},
{
"Level": "2",
"FloorUse": "RESTAURANT",
"Area": 98.57,
"NavPerM2": 62,
"Nav": 6111.34
}
]
},
{
"PublicationDate": "17/09/2019",
"PropertyNumber": 1558322,
"County": "CAVAN",
"LocalAuthority": "CAVAN COUNTY COUNCIL",
"Valuation": 10200,
"Category": "OFFICE",
"Uses": "OFFICE (OWN DOOR), -",
"Address1": "23E MAIN STREET",
"Address2": "CAVAN",
"Address3": "CO. CAVAN",
"Address4": "",
"Address5": "",
"CarPark": 0,
"Xitm": 641941.19,
"Yitm": 804875.56,
"ValuationReport": [
{
"Level": "0",
"FloorUse": "OFFICE(S)",
"Area": 127.55,
"NavPerM2": 80,
"Nav": 10204
}
]
},
{
"PublicationDate": "17/09/2019",
"PropertyNumber": 2116802,
"County": "CAVAN",
"LocalAuthority": "CAVAN COUNTY COUNCIL",
"Valuation": 15140,
"Category": "OFFICE",
"Uses": "OFFICE (OWN DOOR), HERITAGE / INTERPRETATIVE CENTRE",
"Address1": "LOCAL NO/MAP REF: 7AB",
"Address2": "MULLAGH",
"Address3": "BAILIEBORO CO. CAVAN",
"Address4": "",
"Address5": "",
"CarPark": 0,
"Xitm": 668656.19,
"Yitm": 785281.05,
"ValuationReport": [
{
"Level": "0",
"FloorUse": "OFFICE(S)",
"Area": 252.49,
"NavPerM2": 60,
"Nav": 15149.4
property_id: unit[property.id]
}
]
}
]
The difficulty I am having is with the ValuationReport attribute which is an array of varying size for each element in the JSON array of properties. So some properties will have a ValuationReport array containing only one object whereas other properties will contain a ValuationReport object with several objects. I would like to save each of these nested objects from each ValuationReport array to a separate unit in my database (unit model).
I have used the JSONPath Gem to try to access the required elements but there is something I am doing wrong.
When I run '$ rails db:seed', it is now running until a point and I am receiving the following errors:
Creating property 2198231
Creating unit.
rails aborted!
TypeError: no implicit conversion of String into Integer
/db/seeds.rb:62:in `[]'
/db/seeds.rb:62:in `block (2 levels) in properties'
/db/seeds.rb:58:in `each'
/db/seeds.rb:58:in `block in properties'
/db/seeds.rb:36:in `each'
/db/seeds.rb:36:in `properties'
/db/seeds.rb:207:in `<top (required)>'
bin/rails:4:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:seed
(See full trace by running task with --trace)
Here are the relevant Gems from my Gemfile:
# REST Client for APIs
gem 'rest-client', '~> 2.1'
# JSONPath - for acessing values in nested JSON objects.
gem 'jsonpath', '~> 0.5.8'
My database schema is below:
ActiveRecord::Schema.define(version: 2020_11_07_130018) do
# These are extensions that must be enabled in order to support this database
enable_extension "plpgsql"
create_table "properties", force: :cascade do |t|
t.date "publication_date"
t.string "property_number"
t.string "county"
t.string "local_authority"
t.decimal "valuation"
t.string "category"
t.string "uses"
t.string "address_1"
t.string "address_2"
t.string "address_3"
t.string "address_4"
t.string "address_5"
t.decimal "car_park"
t.decimal "xitm"
t.decimal "yitm"
t.datetime "created_at", precision: 6, null: false
t.datetime "updated_at", precision: 6, null: false
end
create_table "units", force: :cascade do |t|
t.string "level"
t.string "floor_use"
t.decimal "area"
t.decimal "nav_per_m2"
t.decimal "nav"
t.bigint "property_id"
t.datetime "created_at", precision: 6, null: false
t.datetime "updated_at", precision: 6, null: false
t.index ["property_id"], name: "index_units_on_property_id"
end
add_foreign_key "units", "properties"
end
My seeds.rb file is below. I have tried to use an index (i) to ensure that the loop for the ValuationReport array of objects is looping once for each property and creating all of the units that exist within each property.
require 'rest-client'
require 'json'
require 'jsonpath'
# Define Method - API Request
def properties
response = RestClient.get('https://api.valoff.ie/api/Property/GetProperties?Fields=*&LocalAuthority=CAVAN%20COUNTY%20COUNCIL&CategorySelected=OFFICE&Format=csv&Download=false')
json = JSON.parse(response)
json.each do |property|
puts "Creating property #{property['PropertyNumber']}"
Property.create!(
publication_date: property['PublicationDate'],
property_number: property['PropertyNumber'],
county: property['County'],
local_authority: property['LocalAuthority'],
valuation: property['Valuation'],
category: property['Category'],
uses: property['Uses'],
address_1: property['Address1'],
address_2: property['Address2'],
address_3: property['Address3'],
address_4: property['Address4'],
address_5: property['Address5'],
car_park: property['CarPark'],
xitm: property['Xitm'],
yitm: property['Yitm'],
units:
JsonPath.new('$.ValuationReport').on(property).each do |unit|
puts "Creating unit."
Unit.create!(
level: unit['$.Level'],
floor_use: unit['$.FloorUse'],
area: unit['$.Area'],
nav_per_m2: unit['$.NavPerM2'],
nav: unit['$.Nav'],
property_id: unit['property.id']
)
end
)
end
end
# Call Method
properties
Appreciate your time and help!
In this scenario you do not need JsonPath. I updated your script:
property['ValuationReport']$. from json keysaccepts_nested_attributes in models)require 'rest-client'
require 'json'
require 'jsonpath'
# Define Method - API Request
def properties
response = RestClient.get('https://api.valoff.ie/api/Property/GetProperties?Fields=*&LocalAuthority=CAVAN%20COUNTY%20COUNCIL&CategorySelected=OFFICE&Format=csv&Download=false')
json = JSON.parse(response)
json.each do |property|
puts "Creating property #{property['PropertyNumber']}"
property_model = Property.create!(
publication_date: property['PublicationDate'],
property_number: property['PropertyNumber'],
county: property['County'],
local_authority: property['LocalAuthority'],
valuation: property['Valuation'],
category: property['Category'],
uses: property['Uses'],
address_1: property['Address1'],
address_2: property['Address2'],
address_3: property['Address3'],
address_4: property['Address4'],
address_5: property['Address5'],
car_park: property['CarPark'],
xitm: property['Xitm'],
yitm: property['Yitm']
)
property['ValuationReport'].each do |unit|
puts "Creating unit."
property_model.units.create!(
level: unit['Level'],
floor_use: unit['FloorUse'],
area: unit['Area'],
nav_per_m2: unit['NavPerM2'],
nav: unit['Nav']
)
end
end
end
# Call Method
properties