Search code examples
sqljsonpostgresqlaggregate-functionsentity-attribute-value

Can get an average of values in a json array using postgres?

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"}
  ]
}

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

Something like 

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

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

How can I do this?

Solution

  • Could work like this:

    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;

    The CTE just substitutes for a table like:

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