从 JSON 列计算平均值

我有一个表，其中包含一列 JSON 数据，我想从中提取信息。具体来说，我只想得到平均值。

我所拥有的示例：

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...

我想要的示例：

id      speed_data
391982  1.375
391982  0.875

关于如何让这个查询工作有什么建议吗？

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

你的 json 数组搞乱了，就像@posz 评论了 https://stackoverflow.com/questions/27368219/calculate-average-from-json-column#comment43201338_27368219。必须是：

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]}');

您的查询也以多种方式扭曲。在 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 小提琴。 http://sqlfiddle.com/#!15/723de/9

在即将到来的PG中9.4我们可以用新的来简化json_array_elements_text()（在演员表中也不太容易出错）：

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

更多细节：

• 如何将json数组转为postgres数组？ https://dba.stackexchange.com/a/54289/3684

旁白：将其存储为普通数组会更有效（numeric[], not json）或以规范化模式开始。