Saving content from JSON into database, using ruby

Question

I have a json file like this:

{"business_id": "vcNAWiLM4dR7D2nwwJ7nCA", "full_address": "4840 E Indian School Rd\nSte 101\nPhoenix, AZ 85018", "hours": {"Tuesday": {"close": "17:00", "open": "08:00"}, "Friday": {"close": "17:00", "open": "08:00"}, "Monday": {"close": "17:00", "open": "08:00"}, "Wednesday": {"close": "17:00", "open": "08:00"}, "Thursday": {"close": "17:00", "open": "08:00"}}, "open": true, "categories": ["Doctors", "Health & Medical"], "city": "Phoenix", "review_count": 7, "name": "Eric Goldberg, MD", "neighborhoods": [], "longitude": -111.98375799999999, "state": "AZ", "stars": 3.5, "latitude": 33.499313000000001, "attributes": {"By Appointment Only": true}, "type": "business"}
{"business_id": "JwUE5GmEO-sH1FuwJgKBlQ", "full_address": "6162 US Highway 51\nDe Forest, WI 53532", "hours": {}, "open": true, "categories": ["Restaurants"], "city": "De Forest", "review_count": 26, "name": "Pine Cone Restaurant", "neighborhoods": [], "longitude": -89.335843999999994, "state": "WI", "stars": 4.0, "latitude": 43.238892999999997, "attributes": {"Take-out": true, "Good For": {"dessert": false, "latenight": false, "lunch": true, "dinner": false, "breakfast": false, "brunch": false}, "Caters": false, "Noise Level": "average", "Takes Reservations": false, "Delivery": false, "Ambience": {"romantic": false, "intimate": false, "touristy": false, "hipster": false, "divey": false, "classy": false, "trendy": false, "upscale": false, "casual": false}, "Parking": {"garage": false, "street": false, "validated": false, "lot": true, "valet": false}, "Has TV": true, "Outdoor Seating": false, "Attire": "casual", "Alcohol": "none", "Waiter Service": true, "Accepts Credit Cards": true, "Good for Kids": true, "Good For Groups": true, "Price Range": 1}, "type": "business"}

I am parsing it using this:

require 'json'

req_hash = File.read("test123.json").split("\n").map do |line|
         JSON.parse(line)
       end

a = req_hash.first

I want to save the content under the attributes "business_id","review" and "useful" from this json file into a database. How can I do this using Ruby

Answer 1

A popular persistence gem is Sequel. It handles all of the popular SQL-databases. Depending on what database you're using, you may need to install a gem to handle that database.

Here's an example script that sets up a in-memory sqlite database, inserts some data to it and then reads that data.

require 'json'
require 'sequel'

input = [
  '{"business_id": "vcNAWiLM4dR7D2nwwJ7nCA", "full_address": "4840 E Indian School Rd\nSte 101\nPhoenix, AZ 85018", "hours": {"Tuesday": {"close": "17:00", "open": "08:00"}, "Friday": {"close": "17:00", "open": "08:00"}, "Monday": {"close": "17:00", "open": "08:00"}, "Wednesday": {"close": "17:00", "open": "08:00"}, "Thursday": {"close": "17:00", "open": "08:00"}}, "open": true, "categories": ["Doctors", "Health & Medical"], "city": "Phoenix", "review_count": 7, "name": "Eric Goldberg, MD", "neighborhoods": [], "longitude": -111.98375799999999, "state": "AZ", "stars": 3.5, "latitude": 33.499313000000001, "attributes": {"By Appointment Only": true}, "type": "business"}',
  '{"business_id": "JwUE5GmEO-sH1FuwJgKBlQ", "full_address": "6162 US Highway 51\nDe Forest, WI 53532", "hours": {}, "open": true, "categories": ["Restaurants"], "city": "De Forest", "review_count": 26, "name": "Pine Cone Restaurant", "neighborhoods": [], "longitude": -89.335843999999994, "state": "WI", "stars": 4.0, "latitude": 43.238892999999997, "attributes": {"Take-out": true, "Good For": {"dessert": false, "latenight": false, "lunch": true, "dinner": false, "breakfast": false, "brunch": false}, "Caters": false, "Noise Level": "average", "Takes Reservations": false, "Delivery": false, "Ambience": {"romantic": false, "intimate": false, "touristy": false, "hipster": false, "divey": false, "classy": false, "trendy": false, "upscale": false, "casual": false}, "Parking": {"garage": false, "street": false, "validated": false, "lot": true, "valet": false}, "Has TV": true, "Outdoor Seating": false, "Attire": "casual", "Alcohol": "none", "Waiter Service": true, "Accepts Credit Cards": true, "Good for Kids": true, "Good For Groups": true, "Price Range": 1}, "type": "business"}'
].join("\n")

class Migration
  def migrate!(db, table_name)
    db.create_table?(table_name) do |db|
      primary_key :id
      String :business_id
      # Add other columns here
    end
  end
end

table_name = :reviews
database = Sequel.sqlite

Migration.new.migrate!(database, table_name)

Review = Class.new(Sequel::Model(:reviews))

class DbWriter

  def write_review(hash)
    data = extract_data(hash)
    Review.create(data)
  end

  def extract_data(input)
    {}.tap do |output|
      output[:business_id] = input["business_id"]
      # Insert other wanted data
    end
  end

end

writer = DbWriter.new

input.split("\n").each do |data|
  parsed = JSON.parse(data)
  writer.write_review(parsed)
end

puts Review.all.inspect

# => [
#     #<Review @values={:id=>1, :business_id=>"vcNAWiLM4dR7D2nwwJ7nCA"}>,
#     #<Review @values={:id=>2, :business_id=>"JwUE5GmEO-sH1FuwJgKBlQ"}>
#    ]

Answer 2

You could use MongoDB, which stores documents as JSON. Below is some basic code to connect to your database and to write and retrieve entries (assuming business_id is unique)

The beauty of the ruby interface is that what goes in and what comes out are lists and hashes, so you can use them directly.

Note, you need to have the mongod (mongo daemon) running.

require 'mongo'

class MongoWriter
  def initialize(host='localhost', port=27017)
    @mongo_client = MongoClient.new(host, port) #connect to the dbhost
    @db = @mongo_client.db("test")              #connect to your database
    @posts = @db["posts"]                       #connect to your collection
  end

  def writepost(post, comments=[])
    #write the entry if "business_id" doesn't exist
    @posts.insert(post) if @posts.find_one({"business_id"=>post["business_id"]}) == nil
  end

  def find(business_id)
    #find a post with "business_id"
    @posts.find_one({"business_id"=>post["business_id"]})
  end
end