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

Question

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?

I didn't receive any error! everything executes fine. however, the data stored in the database is something of form "["{\"value\":\"-0.000873\"}", "{\"value\":\"-0.001745\"}", "{\"value\":\"-0.002618\"}", "{\"value\":\"-0.003491\"}", "{\"value\":\"-0.004363\"}", "{\"value\":\"-0.005236\"}", "{\"value\":\"-0.006109\"}", "{\"value\":\"-0.006981\"}"] — Pragun
– Pragun, Commented Oct 26, 2016 at 6:25
can anybody tell me how do you use html_safe from rails in ruby? — Pragun
– Pragun, Commented Oct 26, 2016 at 6:26
are you trying to create a single row with all the values? that kinds of defeats the purpose of using a database — yoavmatchulsky
– yoavmatchulsky, Commented Oct 26, 2016 at 6:27
Isn't this what you want? can you just use JSON.parse to parse the record and retrieve the original form? — Edmund Lee
– Edmund Lee, Commented Oct 26, 2016 at 6:27

Edmund Lee · Accepted Answer · 2016-10-26 17:10:54Z

3

Postgres persists JSON type as a JSON string. This is a string that you can get by calling #to_json on most objects in ruby. Here's a link to how postgres deal with JSON type in more details.

https://www.postgresql.org/docs/9.6/static/datatype-json.html

Because it's persisted as a JSON string, all the quotes will be escaped. That's why something like this

{ a: 1 }

will become this

"{\"a\":1}"

The \ is for escaping the quote so that it does not end the string.

When you retrieve it from postgres (I personally never used JSON type in PG but I believe it works similarly as JSON.parse on this string), you won't see the slashes any more. And the above process is done by postgres.

edited Oct 26, 2016 at 17:10

answered Oct 26, 2016 at 6:44

Edmund Lee

2,59424 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pragun Over a year ago

storing things with a escape quote in the database doesn't seem like a good practice. Thank you though :) cheers!

Dhurba Baral · Accepted Answer · 2016-10-26 06:47:57Z

2

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

answered Oct 26, 2016 at 6:47

Dhurba Baral

5595 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related