All we need is an easy explanation of the problem, so here it is.
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".
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You need to unnest the JSON array with
You can do this in a correlated subquery:
SELECT (SELECT AVG(value::decimal(18,5)) FROM jsonb_array_elements(value) ) AS avg_value FROM answers;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂