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

Question

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?

The problem is, the data is very difficult to parse. In the full data set, "Items" is just one attribute of a larger JSON object. Plus, the attributes of each record vary widely, there are probably fifty different attributes per record, and you never know which will be present at any given time. I'd love to talk with you more about it if you're willing to help me figure out what to use to store it in the db — johncorser, Aug 26 '14 at 19:18
So it is not _well structured_ as in your sample. As the alternatives are somewhat involved then JSON might be a good option. Check this answer: http://stackoverflow.com/a/876459/131874 — Clodoaldo Neto, Aug 26 '14 at 22:53

score 3 · Accepted Answer · edited May 23 '17 at 12:17

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
);

json_array_elements() (Postgres 9.3+) to unnest the json array is instrumental.
I am using an implicit JOIN LATERAL here. Much like in this related example:
- Query for element of array in JSON column
For an index to support this kind of query consider this related answer:
- Index for finding an element in a JSON array
For details on how to best store EAV data:
- Is there a name for this database structure?

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

1 Answers1

Linked