Average from multiple arrays in Postgres

Question

I want to calculate the average from multiple (json) arrays in Postgres.

I have the following table:

CREATE TABLE marks (student varchar(48), year int , testresult json);

INSERT INTO marks 
VALUES ('John',2017,'[7.3,8.1,9.2]'),
      ('Mary', 2017, '[6.1,7.4,5.6]'), 
     ('Tim',2017,'[6.3,5.6,8.3]');

There are 3 students in 2017 that all have taken 3 tests. I want to calculate the average of the tests for all students in 2017 (with n precision).

Have tried it myself, and only achieved the following until now: http://www.sqlfiddle.com/#!15/58f38/44

So I want to get the following result:

student|  year | averages   
-------+-------+-----------
All    |  2017 | [6.567, 7.033, 7.700]

Lukasz Szozda · Accepted Answer · 2018-05-11 17:56:51Z

1

You could use unnest, do calculation and aggregate back to JSON format:

WITH cte AS (
   SELECT 'All' AS student, year, AVG(unnest::decimal(10,2)) AS av
   FROM marks,
   unnest(ARRAY(SELECT json_array_elements_text(testresult))) with ordinality
   GROUP BY year, ordinality
)
SELECT student, year, array_to_json(array_agg(av::decimal(10,3))) AS averages
FROM cte
GROUP BY student, year;

Output:

+---------+------+---------------------+
| student | year |      averages       |
+---------+------+---------------------+
| All     | 2017 | [6.567,7.033,7.700] |
+---------+------+---------------------+

DBFiddle Demo

edited May 11, 2018 at 17:56

answered May 11, 2018 at 17:51

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

web_student Over a year ago

Great! Thanks for your fast and helpful answer. So as I understand correctly, the array is split and the ordinality keeps the original order? And then the averages are concatenated to a new array again?

Lukasz Szozda Over a year ago

@web_student Yes, exactly.

web_student Over a year ago

I have tried this query and it works. Expect when I try I it with a large dataset (30K+ records) and when the averages json column contains a large json array (60k+ elements) I can't execute the query, getting the following error: invalid memory alloc request size. Is this only due to the large size of the dataset?

Lukasz Szozda Over a year ago

@web_student Well, number of rows matters.

pstanton Over a year ago

i believe you should also have an "order by ordinality"

|

Collectives™ on Stack Overflow

Average from multiple arrays in Postgres

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related