Query Result into JSON in Rails

Question

I want to store the query result for the below query

"SELECT sum( CASE WHEN properties like '%abcd%' THEN 1 ELSE 0 END) 'abcd',
        sum( CASE WHEN properties like '%gef%' THEN 1 ELSE 0 END) 'gef',
        sum( CASE WHEN properties like '%hij%' THEN 1 ELSE 0 END) 'hij',
        sum( CASE WHEN properties like '%lmn%' THEN 1 ELSE 0 END) 'lmn' from events"

as JSON in the form

{'abcd': 1, 'gef' : 2, 'hij' : 1, 'lmn' : 3}

in Rails.

I use count_result = ActiveRecord::Base.connection.execute(sql) which returns only the values and not the keys. I get the results in the format [1,2,1,3].

How to obtain the key values and store them into json format?

max · Accepted Answer · 2017-10-26 15:15:44Z

2

Use ActiveRecord::Base.connection.exec_query to get a ActiveRecord::Result object which can be turned into a hash:

result = ActiveRecord::Base.connection.exec_query("SELECT sum( CASE WHEN properties like '%abcd%' THEN 1 ELSE 0 END) 'abcd',
        sum( CASE WHEN properties like '%gef%' THEN 1 ELSE 0 END) 'gef',
        sum( CASE WHEN properties like '%hij%' THEN 1 ELSE 0 END) 'hij',
        sum( CASE WHEN properties like '%lmn%' THEN 1 ELSE 0 END) 'lmn' from events")

result.to_h # => { 'abcd' => 1, 'hij' => 0 ... }

To convert the values to a JSON string you just call .to_json on the hash. But if you are using a native JSON column type you do not need to manually convert it.

answered Oct 26, 2017 at 15:15

max

103k15 gold badges113 silver badges177 bronze badges

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

Collectives™ on Stack Overflow

Query Result into JSON in Rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related