ruby-on-rails - ruby rails : How can I use JSONPath to access (and save to database) nested objects/properties within a JSON array?

Question

我正在尝试从对象的 JSON 数组中将数据播种到我的数据库中。我有两个单独的数据库表 - Property 和 Unit(一个 Property 有很多 Units)。我已经能够通过请求 JSON 数据的 API 成功地为属性信息(属性模型)执行此操作，然后将其播种到数据库，但是 ValuationReport 属性在我的前端显示为字符串形式的 JSON 对象。

这是我正在使用的 JSON 数据的示例(出于演示目的，我将此示例限制为包含 3 个对象的数组):

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

我遇到的困难是 ValuationReport 属性，它是 JSON 属性数组中每个元素的不同大小的数组。因此，某些属性将具有仅包含一个对象的 ValuationReport 数组，而其他属性将包含具有多个对象的 ValuationReport 对象。我想将每个 ValuationReport 数组中的每个嵌套对象保存到我的数据库(单元模型)中的一个单独单元。

我已经使用 JSONPath Gem 尝试访问所需的元素，但我做错了什么。

当我运行“$ rails db:seed”时，它现在一直运行到某个点，我收到以下错误:

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)

这是我的 Gemfile 中的相关 Gem:

# REST Client for APIs
gem 'rest-client', '~> 2.1'

# JSONPath - for acessing values in nested JSON objects.
gem 'jsonpath', '~> 0.5.8'

我的数据库架构如下:

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

我的 seeds.rb 文件在下面。我尝试使用索引 (i) 来确保 ValuationReport 对象数组的循环针对每个属性循环一次并创建每个属性中存在的所有单元。

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

感谢您的宝贵时间和帮助!

Answer 1

在这种情况下，您不需要 JsonPath。我更新了你的脚本:

• 移除 JsonPath 以支持使用 property['ValuationReport']
• 从 json 键中删除了 $.
• 首先您应该创建一个 Property，然后在 Units 中使用它的 ID(或者您可以在模型中设置 accepts_nested_attributes)

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