Sample Header Ad - 728x90

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