Calculating Average Value of JSONB array in Postgres
0
votes
1
answer
1622
views
I have a column called "value" in my "answers" table.
| value |
|---------|
| [1,2] |
| |
| [1,2,3] |
The type of "value" is "jsonb".
I want to get the average value of each array in each row:
SELECT avg(value) AS avg_value
FROM answers
But this doesn't work because avg() is not a jsonb function. I've tried:
SELECT avg(value::integer[]) as avg_value
FROM answers
i.e. tried to cast the jsonb arrays into integer arrays and then taking the avg, but I get the following error: "cannot cast type jsonb to integer[]. null".
Any ideas?
Asked by user236222
Jul 30, 2021, 09:44 AM
Last activity: Jul 30, 2021, 12:00 PM
Last activity: Jul 30, 2021, 12:00 PM