Rails JSON Response Issue

Question

I'm trying to build a simple API using Ruby on Rails which returns some statistics about tickets from an OTRS database.

Currently when I call my API for the following call, it is returning the below response.

API Call

http://localhost:3000/api/openvsclosed

Current Response

[[1,1,1],[2,2,2]]

I was wondering how I could get this to appear in the correct format of:

Desired Response

[
   {
      "Id":1,
      "Opened":1,
      "Closed":1
   },
   {
      "Id":2,
      "Opened":2,
      "Closed":2
   }
]

Current Controller

def openvsclosed
        sql =   "SELECT
                    @ROW := @ROW + 1 AS Id,
                    COUNT(t.tn) AS Opened,
                    COUNT(t.tn) AS Closed
                FROM
                    ticket t
                    CROSS JOIN (SELECT @ROW := 0) AS Row
                WHERE
                    t.create_time > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                GROUP by
                    DAY(t.create_time)
                ORDER by
                    t.create_time;"
        records = ActiveRecord::Base.connection.execute(sql)
        render json: records
    end

I'm quite new to using Ruby on Rails so I am most likely missing something obvious / big here.

Answer 1

I would recommend checking out jbuilder - you can customize a json template with whatever format you wish for the records you're returning from the controller to the json view.

In this case, you could do something like:

json.array! @records do |json, record|
  json.id record.id
  json.opened record.opened
  json.closed record.closed
end