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?

• Clodoaldo Neto: Do not use JSON for well structured data. Very Bad Idea.
• johncorser: 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
• Clodoaldo Neto: 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: stackoverflow.com/a/876459/131874

Answer 1

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?