Calculating a normalized average with Rails using Postgres JSON column

Question

I have a large stats table in a Postgres DB where the schema looks like this:

player_id: integer,
region: string,
position: string,
league: string,
stats: json

I used this blog post to build the following query to find the average of a given attribute in the stats json object (using as an example the 'hits' stat):

attributes = {region: 'x', position: 'y', league: 'z'}
Stat.where(attributes).average("cast(payload ->> 'hits' as integer)").to_f

That works well and it's fast. Some of the stats, however, need to be built as a function of time the player was in game. Given a stats object with the following format:

{
  "kills":10
  "time_played":120 # seconds
}

I would want to get a value of 5 kills per minute (10 / (120/60)) assuming that was the only stat found (it would be much more, that's why I'm doing average, but for the sake of a simple example...)

Here I'm quite stuck. I know that sql has no trouble dividing things, but throwing in the JSON has me confused. I've tried this:

Stat.where(attributes).average("cast(payload ->> 'kills' as integer) / (cast(payload ->> 'time_played' as integer) / 60)").to_f

it returns 0 - and I'm not really sure how to debug this statement. Any ideas?

solution:

this is what ended up working, with help from EgonWilzer:

def query_for(stat, per_minute: false)
  base = "cast(payload ->> '#{stat}' as float)"

  return base unless per_minute
  base + " / (cast(payload ->> 'time_played' as float) / 60)"
end

The problem is probably the division integer / integer, which will always return 0 if the second one is bigger. Maybe you could try it with as real instead of as integer? — IngoAlbers
– IngoAlbers, Commented Sep 1, 2015 at 14:15
i take it back - i had a mistake (wasn't dividing the time_played bit by 60!) and that does seem to work - if you add it as an answer i'll happily accept it. cheers — dax
– dax, Commented Sep 1, 2015 at 14:48

IngoAlbers · Accepted Answer · 2015-09-01 14:51:29Z

1

The problem is the division integer / integer, which will always return 0 if the second one is bigger.

You could probably get the expected result with as real instead of as integer.

answered Sep 1, 2015 at 14:51

IngoAlbers

5,8026 gold badges33 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

Calculating a normalized average with Rails using Postgres JSON column

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