Ruby on Rails:如何使用JSONPath访问(并保存到数据库)JSON数组中的嵌套对象/属性? [英] Ruby on Rails: How can I use JSONPath to access (and save to database) nested objects/properties within a JSON array?

查看：55
本文介绍了Ruby on Rails:如何使用JSONPath访问(并保存到数据库)JSON数组中的嵌套对象/属性?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试从对象的JSON数组将数据播种到我的数据库中.我有两个单独的数据库表-属性和单位(一个属性有许多单位).通过API请求JSON数据，然后将其播种到数据库，我已经能够成功地针对属性信息(属性模型)执行此操作，但是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)

以下是我的宝石文件中的相关宝石:

# 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

感谢您的时间和帮助！

解决方案

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

  • 删除了JsonPath，转而使用property['ValuationReport']
  • 从json键中删除了$.
  • 首先，您应该创建一个属性，然后在单位中使用其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

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:

  • removed JsonPath in favour of using property['ValuationReport']
  • removed $. from json keys
  • first you should create a Property and then use its ID in Units (alternatively you can setup accepts_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

这篇关于Ruby on Rails:如何使用JSONPath访问(并保存到数据库)JSON数组中的嵌套对象/属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆