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

The JSON file I have is very large and I have multiple reviews from the same database, so what I am trying to do is perform a couple of operations on the attributes stated above and store them in another database so that I can then match the reviews with the same business id's. I then need to perform some more operations on them. I have been looking for quite some time now but I can't find any way to do it. I was thinking of using something like mySql but I am a ruby noob so I am not sure about how to do it. — anonn023432
– anonn023432, Commented Nov 11, 2014 at 12:01
There are plenty of resources out there on ruby and databases, have you tried Googling? — Jesper
– Jesper, Commented Nov 11, 2014 at 12:19
As I said "I have been looking for quite some now", I tried googling but couldn't find anything, that's why I am asking here. And I now there are plenty of resources on ruby and databases but I can't find anything to help me, except datamapper, but I am not sure if that's the right thing — anonn023432
– anonn023432, Commented Nov 11, 2014 at 12:29

Jesper · Accepted Answer · 2014-11-12 17:09:33Z

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"}>
#    ]

Ritwik Bose · Accepted Answer · 2014-11-12 16:52:30Z

0

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

edited Nov 12, 2014 at 16:52

answered Nov 11, 2014 at 16:23

Ritwik Bose

11 bronze badge

1 Comment

Jesper Over a year ago

Shouldn't that module be a class?

Collectives™ on Stack Overflow

Saving content from JSON into database, using ruby

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related