CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordsequelruby-datamapper
iconoclast
iconoclast

Reputation: 22620

Turn results of a raw SQL query to JSON (and CSV)

I need to pull data from a legacy SQL Server database and turn it into JSON. I have SQL queries already written (for legacy versions of reports, that I'm replacing) that extracts exactly what I need.

It is of course easy to turn ActiveRecord objects to JSON (and CSV), but I don't see any good way to do what I need to do, since all the ActiveRecord methods involving raw SQL seem to be aimed at returning ActiveRecord objects, and this data doesn't necessarily logically correspond to a class, and I don't need to persist it anywhere additional. I just want to perform the SQL query and return JSON and CSV and be done with it.

Upvotes: 2

Views: 5181

Answers (2)

accelBurst
accelBurst

Reputation: 61

in your controller, you could add:

render json: @result

or

render :json => @result

and that would render something like this: {id: 1, name: "user name", color: "red"}

Upvotes: 6

Shawn Bush
Shawn Bush

Reputation: 642

I'd recommend sequel.

From Querying with Sequel:

require 'sequel'

DB = Sequel.connect # database information goes here

class Thing < Sequel::Model
end

result_objects = DB["SELECT * FROM things"].all

Then simply parse the result_objects into JSON:

require 'json'

hashes = result_objects.collect { |ro|
  ro.to_hash
}

JSON.generate(hashes)

Upvotes: 4

Related Questions