current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
    
Do not use JSON for well structured data. Very Bad Idea. –  Clodoaldo Neto Aug 26 '14 at 18:24
    
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
1  
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 –  Clodoaldo Neto Aug 26 '14 at 22:53

1 Answer 1

up vote 1 down vote accepted

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
);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.