I have a column called "value" in my "answers" table.
| value |
|---------|
| [1,2] |
| [1] |
| [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?