Calculate average from JSON column

Question

I have a table with a column of JSON data that I want to extract information from. Specifically I just want to get the average value.

Example of what I have:

id      speed_data
391982  [{"speed":1.3,"speed":1.3,"speed":1.4,"speed":1.5...
391983  [{"speed":0.9,"speed":0.8,"speed":0.8,"speed":1.0...

Example of what I want:

id      speed_data
391982  1.375
391982  0.875

Any suggestions on how to get this query to work?

select t.*, avg(x.speed)
from tbl t,
    json_array_elements(a->'speed') x
order by random()
limit 1

What result are you getting? Are your values being converted to ints? — Dmitri Goldring, Dec 09 '14 at 00:38
You won't be able to do this. You have semantically invalid json: in a json object, every key must come up only once; this json is syntactically valid, but only the last pair (with key `"speed"`) will be seen in results (after parsing). — pozs, Dec 09 '14 at 09:44

score 4 · Accepted Answer · edited May 23 '17 at 11:46

Your json array is messed up, like @posz commented. Would have to be:

CREATE TABLE tbl (id int, speed_data json);

INSERT INTO tbl VALUES
  (391982, '{"speed":[1.3,1.3,1.4,1.5]}')
, (391983, '{"speed":[0.9,0.8,0.8,1.0]}');

You query is twisted in multiple ways, too. Would work like this in pg 9.3:

SELECT t.id, avg(x::text::numeric) AS avg_speed
FROM   tbl t
     , json_array_elements(speed_data->'speed') x
GROUP  BY t.id;

SQL Fiddle.

In the upcoming pg 9.4 we can simplify with the new json_array_elements_text() ( also less error-prone in the cast):

SELECT t.id, avg(x::numeric) AS avg_speed
FROM   tbl t
     , json_array_elements_text(speed_data->'speed') x
GROUP  BY t.id;

More Details:

How to turn json array into postgres array?

Aside: It would be much more efficient to store this as plain array (numeric[], not json) or in a normalized schema to begin with.

Calculate average from JSON column

1 Answers1

Linked