Given an array column in a table of a PostgreSQL database containing the following:
{{765,4},{767,3},{569,5},{567,3},{725,5}}
How could I calculate the sum of all second elements of each subarray, i.e. 4+3+5+3+5
Add a comment
|
2 Answers
You can try using UNNEST which expands an array to a set of rows, and filtering by the row number:
SELECT *, (
SELECT SUM(v)
FROM UNNEST(array_column) WITH ORDINALITY a(v, n)
WHERE n % 2 = 0
) FROM your_table;
3 Comments
mick
Thank you clemens. However, when you UNNEST the array_column, you get a column of integers, not an array, so it cannot be subscripted: The result of the UNNEST is: 765 4 767 3 569 5 567 3 725 5
clemens
Ok, this is a little bit more (dirty) tricky. I adapted my query, and added the row number to identify the even rows from unnest.
mick
Thanks, Was not aware of ORDINALITY, but it doesn't resolve my real issue The array contains pairs of questionid and user_response, i.e. {765,4} questionid=765/response=4. I didn't provide enough info. I was avoiding using a user_respones table (userid, questionid, response) by putting an array or json in a field in the user table. I need to query for the sum of various responses by groups of questionid's. Easy with a user_response table, but was trying to take advantage of Postgresql's array or json data types. I probably should just use the user_respones table approach and be done with it.
I was able to resolve my objective presented here by using jsonb array.
The jsonbArray [{"an": 4, "qid": 765}, {"an": 3, "qid": 767}, {"an": 5, "qid": 569}, {"an": 3, "qid": 567}, {"an": 5, "qid": 725}]
The query that accomplishes the objective:
WITH answers as (
SELECT
(jsonbArray -> 'an')::int as an,
(jsonbArray -> 'qid')::int as qid
FROM (
SELECT jsonb_array_elements(jsonbArray) AS jsonbArray
FROM user where id = 1
) AS s
group by qid, an
)
select sum(an) as score from answers where qid in (765,725)
Result:
score
9