Pragun Pragun - 1 month ago 9
Ruby Question

Ruby with PostgreSQL - Inserting into a JSON field of the database

I'm trying to insert a ruby hash into Postgres JSON column. Basically its a collection of hash in an array. So you have a structure something like:

[{"a":"1"},{"b":"2"},{"c":"3"}] #the length goes on


The existing code for whatever I'm trying to do is:

require 'pg'

freq = 0.05
$test = 0
$sinval = 0
arr = Array.new

@conn = PG.connect(
:dbname => 'test_db',
:user => 'abc',
:port => 6100,
:host => 'localhost'
)

while true do
$test = ($test+freq).round(2)
$sinval = Math.sin($test*(Math::PI/180)).round(6)
hash = {:value=>$sinval.to_s}
arr.push(hash.to_json)
if(arr.length>=1800)
@conn.exec("INSERT INTO sinewave(data) VALUES('#{arr}');")
arr = Array.new
end
end


But now, I'm not being able to store it in the database the way I want. For each row, what I'd like to see is a JSON Array of 1800 objects and the structure be:

[{"a":"1"},{"b":"2"},{"c":"3"}]


I don't know whats wrong. May be
hash = {:value=>$sinval.to_s}
this line is incorrect. Other ways to sort this out?

Answer

If you convert hash object into JSON in ruby, it adds escape character automatically so try converting the whole array of object into JSON instead of hash object.

require 'pg'

freq = 0.05
$test = 0
$sinval = 0
arr = Array.new

@conn = PG.connect(
        :dbname => 'test_db',
        :user => 'abc',
        :port => 6100,
        :host => 'localhost'
        )

while true do
        $test = ($test+freq).round(2)
        $sinval = Math.sin($test*(Math::PI/180)).round(6)
        hash = {:value=>$sinval.to_s}
        arr.push(hash)
        if(arr.length>=1800)
                @conn.exec("INSERT INTO sinewave(data) VALUES('#{arr.to_json}');")
                arr = Array.new
        end
end