可以使用postgres获取json数组中值的平均值吗? [英] Can get an average of values in a json array using postgres?

问题描述

postgres的一大优点是它允许索引到json对象.

One of the great things about postgres is that it allows indexing into a json object.

我有一列数据的格式如下:

I have a column of data formatted a little bit like this:

{"Items":
  [
    {"RetailPrice":6.1,"EffectivePrice":0,"Multiplier":1,"ItemId":"53636"},
    {"RetailPrice":0.47,"EffectivePrice":0,"Multiplier":1,"ItemId":"53404"}
  ]
}

我想做的是找到包含这些数据的每一行的平均RetailPrice.

What I'd like to do is find the average RetailPrice of each row with these data.

类似

select avg(json_extract_path_text(item_json, 'RetailPrice')) 

但实际上我需要对项目json对象中的每个项目执行此操作.因此，对于此示例，查询的单元格中的值为3.285

but really I need to do this for each item in the items json object. So for this example, the value in the queried cell would be 3.285

我该怎么做?

推荐答案

可以像这样工作:

WITH cte(tbl_id, json_items) AS ( 
   SELECT 1
        , '{"Items": [
       {"RetailPrice":6.1,"EffectivePrice":0,"Multiplier":1,"ItemId":"53636"}
      ,{"RetailPrice":0.47,"EffectivePrice":0,"Multiplier":1,"ItemId":"53404"}]}'::json
   )
SELECT tbl_id, round(avg((elem->>'RetailPrice')::numeric), 3) AS avg_retail_price
FROM   cte c
     , json_array_elements(c.json_items->'Items') elem
GROUP  BY 1;

CTE只是代替了像这样的表:

The CTE just substitutes for a table like:

CREATE TABLE tbl (
   tbl_id     serial PRIMARY KEY
 , json_items json
);

• json_array_elements() (Postgres 9.3+)来取消json数组的作用.

  • json_array_elements() (Postgres 9.3+) to unnest the json array is instrumental.

    我正在使用隐式 JOIN LATERAL 在这里.与此相关示例非常相似:

    I am using an implicit JOIN LATERAL here. Much like in this related example:

    • 在JSON列中查询数组元素

    对于支持这种查询的索引，请考虑以下相关答案:

    For an index to support this kind of query consider this related answer:

    • 在JSON数组

    有关如何最好地存储 EAV数据:

    • 此数据库结构是否有名称?

    这篇关于可以使用postgres获取json数组中值的平均值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！